MYSQL ignores not null constraint, inserts null values into table and only displays a warning

Question

I am using MYSQL versoin 14.14 Distrib 5.7.40, for Linux (x86_64). I have created a table with 3 columns and the columns have NOT NULL constraints set. Then when I try to insert null values directly into the table, it gives me an error saying the column cannot be null. However if I try to insert null values using a select statement, then MYSQL inserts a new record and only displays a warning. In the inserted record I see that the MYSQL has substituted NULL with empty string.

DROP TABLE IF EXISTS table1;

DROP TABLE IF EXISTS table2;

CREATE  TABLE IF NOT EXISTS table1 (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
     col_1 VARCHAR(45) NOT NULL ,
     col_2 VARCHAR(45) NOT NULL ,
     PRIMARY KEY (id));
     
CREATE TABLE IF NOT EXISTS table2 LIKE table1;

-- gets inserted as expected
INSERT INTO table1(id, col_1, col_2) VALUES (1,'aaa', 'bbb');
INSERT INTO table2(id, col_1, col_2) VALUES (2,'ccc', 'ddd');

-- does not get inserted as expected
INSERT INTO table1(col_1, col_2) VALUES ('xxx', NULL); -- ERROR 1048 (23000): Column 'col_2' cannot be null

-- gets inserted which I didn't expect, it gives a warning but inserts
INSERT INTO table1(id, col_1, col_2) SELECT id, col_1, NULL FROM table2 where id=2; -- Query OK, 1 row affected, 1 warning (0.07 sec)

I tried displaying the warning after the query execution. And it said, "Warning | 1048 | Column 'col_2' cannot be". But still MYSQL didn't stop me from inserting values into the table, it substituted empty string.

mysql> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_2' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 | aaa   | bbb   |
|  2 | ccc   |       |
+----+-------+-------+
2 rows in set (0.01 sec)

Answer 1

Why am I answering my own question? I had this question and found similar questions. The answers briefly pointed me to SQL STRICT MODE. But none of the answers are comprehensive, so I had to do my own digging. I have learnt how MYSQL NOT NULL CONSTRAINT, and the SQL STRICT MODE work together, and I would like to detail my answer here.

First let us understand, what happens when we try to insert NULL values into a column that has a NOT NULL constraint.

table1 has three columns id, col_1, and col_2 which all have NOT NULL constraints

mysql> desc table1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| col_1 | varchar(45)      | NO   |     | NULL    |                |
| col_2 | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

When we try to insert explicit NULL values into the column, MYSQL will throw an error.

mysql> insert into table1(col_1, col_2) values (NULL,'xxx');
ERROR 1048 (23000): Column 'col_1' cannot be null

However if we try to skip a column (not inserting values), MYSQL will not give an error. Instead it will insert the record and only give a warning.

mysql> insert into table1(col_2) values ('xxx');
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'col_1' doesnt have a default value |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  4 |       | xxx   |
+----+-------+-------+
1 row in set (0.00 sec)

We could also use a select query to insert values into a table. And in this case again MYSQL only gives a warning and inserts the record.

mysql> insert into table1(col_1, col_2) select NULL, col_2 from table2;
Query OK, 1 row affected, 1 warning (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_1' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  4 |       | xxx   |
|  5 |       | ddd   |
+----+-------+-------+
2 rows in set (0.00 sec)

Now let us understand the SQL Modes in MYSQL. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. Please refer the MYSQL documentation for the details.

To check the current SQL mode, try the following.

mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

To set MYSQL to strict mode, try the following.

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

After MYSQL is set to strict mode, if you tried the previous insert statements they would fail with errors.

mysql> insert into table1(col_2) values ('xxx');
ERROR 1364 (HY000): Field 'col_1' doesnt have a default value
mysql> insert into table1(col_1, col_2) select NULL, col_2 from table2;
ERROR 1048 (23000): Column 'col_1' cannot be null

One more thing is that SQL_MODE will be set only for the current MYSQL session. If you want to set it forever, then you have to either set it in the GLOBAL or the MYSQL config files.

Try the following to set strict mode as a global SQL mode.

mysql> set global sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

I hope this very long answer sheds some light on the SQL mode and inserts in MYSQL. Please vote up the question and answer if you find it helpful.