1.8.5.2 Constraint NOT NULL and DEFAULT values

To be able to support easy handling of non-transactional tables all fields in MySQL have default values.

If you insert a 'wrong' value in a column like a NULL in a NOT NULL column or a too big numerical value in a numerical column, MySQL will instead of giving an error instead set the column to the 'best possible value'. For numerical values this is 0, the smallest possible values or the largest possible value. For strings this is either the empty string or the longest possible string that can be in the column.

This means that if you try to store NULL into a column that doesn't take NULL values, MySQL Server will store 0 or '' (empty string) in it instead. This last behaviour can, for single row inserts, be changed with the -DDONT_USE_DEFAULT_FIELDS compile option.) See section 2.3.3 Typical configure Options. This causes INSERT statements to generate an error unless you explicitly specify values for all columns that require a non-NULL value.

The reason for the above rules is that we can't check these conditions before the query starts to execute. If we encounter a problem after updating a few rows, we can't just rollback as the table type may not support this. The option to stop is not that good as in this case the update would be 'half done' which is probably the worst possible scenario. In this case it's better to 'do the best you can' and then continue as if nothing happened. In MySQL 5.0 we plan to improve this by providing warnings for automatic field conversions, plus an option to let you roll back statements that only use transactional tables in case one such statement does a field assignment that is not allowed.

The above means that one should generally not use MySQL to check field content, but instead handle this in the application.

User Comments

Add your own comment.