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:

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 affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2

Running a show warnings command gives us this:

+---------+------+---------------------------------+
| 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:

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”:

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:

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

Or, don’t specify the column at all:

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

Hopefully this will help you avoid the confusion we had when we first looked at this behavior.

Want to hear more about what I’ve learned about MySQL? Well, I’m writing a book about it! You can subscribe below and I’ll send you updates. I’ll also send out stuff on MySQL like tips and tutorials.



 No spam

Tagged with:
Posted in Databases

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>