Why is MySQL changing null to zero?

Question

I'm completely new to databases and SQL.
I have a column marked NOT NULL.
When I forget to give a value to that column when doing an INSERT, it inserts a 0 value in that column.
Is there anything I can do to make it return an error instead of changing the NULL to 0?

Answer 1

You want to have a look at the SQL_MODE configuration. It allows you to define how strict MySQL handles such things. By default it is pretty lenient which is not what I usually want. It also depends on the data type, especially with Dates it is less than optimal by default.

I personally go for STRICT_ALL_TABLES usually.

See the MySQL manual (5.0) here:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Answer 2

There are two possible reasons for this:

• You have a default value defined for that column
• Your column is INT NOT NULL and casts NULL to INT, which results in 0

The second situation seems to be the case here, obviously. If you want to get an error instead, you could change the SQL_MODE to be strict. An other possibility is to do the input validation in your progam rather than leaving it to SQL.

Answer 3

I guess you have a DEFAULT value for that column