Jan,
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
>
