MySQL Insert Select does not enforce NULL constraint

Question

I have a problem with MySQL 5.6 InnoDb, ignoring the NOT NULL foreign key when running INSERT INTO xxx (col) SELECT ... The restriction is enforced when running insert statements in other formats. Foreign key checks are included, and sql_mode = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

Here is an example:

 CREATE TABLE Test_Parent ( id BIGINT(18) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, dummy VARCHAR(255) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT 'Test parent table'; CREATE TABLE Test_Child ( id BIGINT(18) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, fid BIGINT UNSIGNED NOT NULL, FOREIGN KEY Fk_Test_Parent_01(fid) REFERENCES Test_Parent(id) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT 'Test child table'; INSERT INTO Test_Parent(dummy) VALUES ('test'); ## Here where the FK constraint should be enforced but isn't ## INSERT INTO Test_Child(fid) SELECT id FROM Test_Parent WHERE dummy = 'missing value'; 1 row affected in 5ms ## Running an insert with a different format, the constraint is enforced ## INSERT INTO Test_Child(fid) VALUES (null); Column 'fid' cannot be null ## Running this format, the foreign key is also enforced ## INSERT INTO Test_Child(id, fid) VALUES (123, (SELECT id FROM Test_Parent WHERE dummy = 'missing value')); Column 'fid' cannot be null 

I do not understand why MySQL will force the use of a foreign key for 2 of the three insert statements. Any ideas?

Answer 1

The error message that your client introduces can be a source of confusion. IntelliJ reported this message in the comment thread you were talking about, but I ran your well-defined test tables in both MySQL 5.6.35 and 5.7.16-ubuntu, and in both versions the statement in question reported 0 lines affected:

 mysql > INSERT INTO Test_Child(fid) -> SELECT id -> FROM Test_Parent -> WHERE dummy = 'missing value'; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 

So, looking at the message about the affected rows affected, what is actually happening here is that the SELECT part of your INSERT...SELECT does not contain rows, and therefore MySQL is not trying to insert any row. Consequently, there was no violation of foreign key restrictions.

The format of INSERT INTO...SELECT slightly different from your later example:

 INSERT INTO Test_Child(id, fid) VALUES (123, (SELECT id FROM Test_Parent WHERE dummy = 'missing value')); 

... because in this case, the numeric literal 123 forces one line to be inserted, and it matches the null value returned from the subquery. So null tries to insert and causes a constraint violation.

If you force INSERT...SELECT return a row with a null value, you may crash due to a violation of the constraint:

 INSERT INTO Test_Child (fid) -- Return a literal NULL row... SELECT NULL as id FROM Test_Parent -- Column fid cannot be null