Re: NOT NULL column behaves strangely

[Michael Stassen]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]