I just wanted to know if somebody could explain this.
I was just testing my code and didn't check for empty input fields (I know I have to, but just testing).. In my database table, all the fields are NOT NULL, and I was expecting a exception because I wasn't inserting anything.. But it turns out that MySQL inserts all with blank values, also, from MySQL workbench is the same thing..
Is there a way to prevent this? (From a MySQL perspective)
This behavior, although atypical, is quite well documented:
Inserting NULL into a column that has been declared NOT NULL. For multiple-row
INSERTstatements orINSERT INTO ... SELECTstatements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.INSERT INTO ... SELECTstatements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-rowINSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)
So, if you want to get an error, use VALUES() with a single row. Alternatively, define a trigger that does the check.
Why does MySQL work this way? I don't know, to differentiate itself from other databases and prevent ANSI-compatibility? More seriously, I assume that this a question of efficiency, and related to the fact that MySQL does not implement check constraints. The NOT NULL declaration is just an example of a check constraint, and these are not supported.
