MySQL Insert Select Doesn't Enforce NOT NULL Constraint

Question

I'm having an issue with MySQL 5.6 InnoDb ignoring a NOT NULL foreign key when running an INSERT INTO xxx (col) SELECT .... The constraint is enforced properly when running insert statements in other formats. Foreign key checks are enabled, and sql_mode = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

Here's 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's 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 don't understand why MySQL will enforce the foreign key for 2 out of the 3 insert statements. Any ideas?

Answer 1

A misleading 1 row affected in 5ms message from your client may be the source of confusion here. In the comment thread you mentioned IntelliJ was reporting that message, 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 affected rows:

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 past the misleading affected rows message, what's really happening here is that the SELECT portion of your INSERT...SELECT statement matches no rows, and therefore MySQL does not make an attempt at inserting any row. There was therefore no foreign key constraint violation.

The format of the INSERT INTO...SELECT differs a little 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 row to be inserted, and it is paired with a null value returned from the subselect. So that null is attempted to insert and causes a constraint violation.

If you force the INSERT...SELECT to return a row with a null value, you can make fail due to a constraint violation:

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