Inserting a NULL into a NOT NULL column used to automatically get the DEFAULT value. I realize that it was non-portable, non-standard behavior, but the code relies on it. How do I get this behavior in the 4.0.x server?
mysql> CREATE TABLE t (a INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t (a) VALUES (NULL); ERROR 1048: Column 'a' cannot be null
Two points:
- Inserting NULL into a NOT NULL column results in an error for a single-row INSERT. The behavior you show is expected.
- Inserting NULL into a NOT NULL column inserts a value when this occurs in a multiple-row INSERT. However, the value inserted is not the DEFAULT value as specified in the column definition, it is the default value for the column *type*.
Run this input using mysql -f db_name:
DROP TABLE t; CREATE TABLE t (i INT NOT NULL DEFAULT 1); INSERT INTO t (i) VALUES(NULL); SELECT * FROM t; INSERT INTO t (i) VALUES(NULL),(NULL); SELECT * FROM t;
The first INSERT will fail.
The second INSERT will insert two values of 0 (not 1).
See last part of:
http://www.mysql.com/doc/en/INSERT.html
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
