List:General Discussion« Previous MessageNext Message »
From:B. Fongo Date:April 14 2004 6:21pm
Subject:AW: NOT NULL column behaves strangely
View as plain text  
Ja! Most RDBMS don't use default values for NOT NULL columns. Rather
they alarm if a statement renders such fields NULL. 

I do agree with Michael. Mysql went a different way in this regard, in
that it assigns default values... It only warns if one tries to
explicitly enter "NULL" in NOT NULL column.

Babs


-----Ursprüngliche Nachricht-----
Von: Michael Stassen [mailto:Michael.Stassen@stripped] 
Gesendet: Mittwoch, 14. April 2004 18:21
An: Ruslan U. Zakirov
Cc: Keith C. Ivey; mysql@stripped; B. Fongo
Betreff: Re: NOT NULL column behaves strangely


Ruslan U. Zakirov wrote:

> Keith C. Ivey wrote:
> 
>> On 14 Apr 2004 at 17:27, B. Fongo wrote:
>>
>>> I expected a warning because of the Token column shouldn't be NULL! 
>>
>> It's not NULL.  It's the empty string, which is the default value, 
>> since you didn't give it a specific default value.  See the "CREATE 
>> TABLE" documentation:
>>
>>     If no DEFAULT value is specified for a column, MySQL
>>     automatically assigns one, as follows. If the column can
>>     take NULL as a value, the default value is NULL. If the
>>     column is declared as NOT NULL, the default value depends on
>>     the column type:     [...]
>>     * For string types other than ENUM, the default value is the
>>     empty string.
>>
>> http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
>>
> I didn't know this too I expect correct behavior: error.

Well, that depends on your definition of "correct".  Some will argue
that 
the SQL standard, whatever that is, defines "correct".  Others will say
that 
  the MySQL documentation defines "correct".  Either way, if you work
with 
MySQL, you need to know what it really does.

Because MySQL supports non-transactional table types, every column has a

default.  So, if you leave a column out of your INSERT, it gets the
default. 
  On the other hand, if you try to explicitly set a NOT NULL column to
NULL 
with a single row INSERT, you get an error.

> So if I want error on such INSERTs I must use 'IS NOT NULL default
NULL'?
> 
>             Best regards. Ruslan. 

No.  You cannot set NULL as the default for a NOT NULL column (at least,
not 
in MySQL).  If you want an error, specify NULL for the column rather
than 
leaving it out of your INSERT.

Michael


Thread
NOT NULL column behaves strangelyB. Fongo14 Apr
  • Re: NOT NULL column behaves strangelyKeith C. Ivey14 Apr
    • Re: NOT NULL column behaves strangelyRuslan U. Zakirov14 Apr
      • Re: NOT NULL column behaves strangelyMichael Stassen14 Apr
        • AW: NOT NULL column behaves strangelyB. Fongo14 Apr
  • Re: NOT NULL column behaves strangelyMartijn Tonies14 Apr
    • AW: NOT NULL column behaves strangelyB. Fongo14 Apr