This question comes up a lot. You should take a look at <http://www.mysql.com/doc/en/constraint_NOT_NULL.html>. The first line is, "To be able to support easy handling of non-transactional tables, all fields in MySQL have default values." Mysql automatically converts missing or illegal values to column defaults, because in the non-transactional case, it cannot roll back when you have an illegal value in the Nth row of a multi row insert.
The solution is to have the program/client verify data before inserting, updating, or loading the db, rather than counting on mysql to reject bad data. After all, there are a lot of ways for data to technically fit a column definition but still be bad in terms of your application. If you can't trust users not to leave a field blank, can you trust them not to enter nonsense? As long as you're validating input anyway, making sure it's not NULL shouldn't be a big deal.
You can turn off defaults for single row inserts by building your own mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option, in which case insert statements will "generate an error unless you explicitly specify values for all columns that require a non-NULL value." But if I understand correctly, this turns off using any defaults, even those you've explicitly set in column definitions. (Someone please correct me if I'm wrong.)
Finally, I'd be shocked if mysql stored an empty string in an int column. Perhaps mysqlcc got confused?
Michael
Jan Magnusson wrote:
Dear List,
Could sombody explain this behaviour of mySQL 4 to me:
Assigning a column as "not null" will AUTOMATICALLY assign (=force upon) it a default value of an empty string ('') if a string or zero (0) if a numerical datatype. Thereby effectively bypassing all and every error message during inserts if missing values in records.
Why ???
- This behaviour in effect means there will never be generated an error during insertion and omitting a column assigned as "not null" because mysql will on its own generate a default value for it.
- What use do I have for the "not null" constraint on the column if it's anyway assigned a value. As it now works it's the same as forcing a default value attribute.
- At first I understood the mySQL documentation (6.3.5.) so that if you just omit the value but still having the default attribute specified in your CREATE TABLE... it would then automatically give it a "default default value". Makes sense.
- This does not seem to apply to PK:s, but all other columns.
BTW, when trying this out with mysqlcc I also managed to generate output where the default value generated by mysql for an int data type column contrary to documentation too was an empty string instead of "0".
Am I now missing something obvious in the logic of this behaviour ???
Jan
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
