One of my coworkers came across a strange quirk in MySQL with default values for not null columns. Take a look at this table:
Create Table Posts
(
PostID Int Auto_Increment Primary Key
,Title Varchar(30) Not Null
,Body Text Not Null
,Summary Varchar(25) Not Null Default ''
);
Note the column Summary that is marked not null but has a default value of an empty string. Now, try to insert a null value into this column.
Insert Into Posts (Title, Body, Summary) Values ('A title', 'A body', null);
You’ll get this error:
ERROR 1048 (23000): Column 'Summary' cannot be null
Now, using an extended insert (where you specify multiple rows in an insert statement), we can insert the same data and it will complete successfully, although there will be warnings.
Insert Into Posts (Title, Body, Summary) Values ('A title', 'A body', null), ('2nd body', '2nd body', null);
Query OK, 2 rows …[Read more]
