List:General Discussion« Previous MessageNext Message »
From:Jan Magnusson Date:October 31 2003 3:52pm
Subject:"not null" and default values confusion
View as plain text  
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 sence.

- This does not seem to apply to PK:s, but all other columns.

BTW, when trying out this 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