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
*************************** 2. row ***************************
PostID: 2
Title: 2nd title
Body: 2nd body
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
3 comments on “MySQL Quirk with Not Null Columns and Default Values
  1. Rohan says:

    Thank you very much for the info..explains a peculiar case we were facing with a query getting executed in the console but not through our application

  2. Amir says:

    Thanks 🙂
    But why don’t you omit “Not Null” !? :/ when you have a default value you can not insert NULL value 😐

  3. Nathansa says:

    There are many situations in real life where an object may or may not have some property, but if it does then its value must be unique, and MySQL s behaviour allows this to be modelled easily.

Leave a Reply

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