MySQL Quirk with Not Null Columns and Default Values

One of my coworkers came across a strange quirk in MySQL with default values for not null columns. Take a look at this table:

1 2 3 4 5 6 7

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.

1	Insert Into Posts (Title, Body, Summary) Values ('A title', 'A body', null);

You’ll get this error:

1	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.

1 2 3 4

Insert Into Posts (Title, Body, Summary) Values ('A title', 'A body', null), ('2nd body', '2nd body', null);   Query OK, 2 rows affected, 2 warnings (0.01 sec) Records: 2  Duplicates: 0  Warnings: 2

Running a show warnings command gives us this:

1 2 3 4 5 6 7

+---------+------+---------------------------------+ | Level   | Code | Message                         | +---------+------+---------------------------------+ | Warning | 1048 | Column 'Summary' cannot be null | | Warning | 1048 | Column 'Summary' cannot be null | +---------+------+---------------------------------+ 2 rows in set (0.00 sec)

But we can see the rows show up just fine:

1 2 3 4 5 6 7 8 9 10 11 12

mysql> select * from Posts\G *************************** 1. row *************************** PostID: 1 Title: A title Body: A body Summary: *************************** 2. row *************************** PostID: 2 Title: 2nd title Body: 2nd body Summary: 2 rows in set (0.01 sec)

Now, just to reiterate: you will get an error and no records will be inserted if you insert a single row with a null value for a not null column with a default value. However, if you insert multiple rows with an extended insert with the same data, the rows will be inserted.

To ensure an error even when inserting multiple rows, set the sql_mode to “strict_all_tables”:

1	Set sql_mode = "strict_all_tables";

There are two correct ways of using the default value when inserting into a not null column. Instead of putting null for Summary, you can use the default keyword, which will insert that column’s default value:

1	Insert Into Posts (Title, Body, Summary) Values ('A title', 'A body', default);

Or, don’t specify the column at all:

1	Insert Into Posts (Title, Body) Values ('A title', 'A body');