List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 1 2003 3:13am
Subject:Re: "not null" and default values confusion
View as plain text  
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
> 

Thread
"not null" and default values confusionJan Magnusson31 Oct
  • Re: "not null" and default values confusionMichael Stassen1 Nov
    • RE: "not null" and default values confusionJan Magnusson1 Nov
  • Re: "not null" and default values confusionMartijn Tonies1 Nov