Omitting the value "NOT NULL" during INSERT, it is 0. Why?

Question

I have the following table structure:

badge_id and interface_id are the main keys .

When I try to perform an INSERT operation with only badge_id , I expected it to fail, but instead, interface_id will default to 0 .

I run the following versions:

Can someone explain why it defaults to zero? Is that not so? Thanks in advance.

Answer 1

Check this out: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Starting in MySQL 5.0.2, if the column definition does not contain an explicit DEFAULT value, MySQL defines the default value as follows:

If a column can be NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If a column cannot accept NULL as a value, MySQL defines the column without an explicit DEFAULT clause. To enter data if the INSERT or REPLACE statement does not contain a value for a column, MySQL processes the column in accordance with the current SQL mode:

If strict SQL mode is not enabled, MySQL sets the column to an implicit default value for the column data type.

Answer 2

The answer is in the documentation for CREATE TABLE :

If the column definition does not contain an explicit DEFAULT value, MySQL defines the default value, as described in section 11.5, “Data Type Default Values”.

Additional information is available in Default values ​​for data types :

Implicit defaults are defined as follows: for numeric types, the default is 0, except that for integers or floating-point types declared with the AUTO_INCREMENT attribute, the following value is used by default in the sequence.