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?

I wonder if you're seeing a misreported "1 row affected" because that `INSERT...SELECT` is not actually resulting in any row insert because the `WHERE` clause doesn't match. If I `SELECT *` from both tables, I see no rows in `Test_Child`...http://sqlfiddle.com/#!9/7413e0/1 — Michael Berkowski, Dec 23 '16 at 00:20
That is, unless your real data and use case differs significantly from the test set you setup for us. — Michael Berkowski, Dec 23 '16 at 00:21
That's right - it reports 1 row as being inserted, but nothing has actually been inserted into the table. I would expect the `NOT NULL` constraint to be enforced in this scenario. Instead, it's as if the exception is swallowed. — adam, Dec 23 '16 at 00:27
But in your real test data, does the `WHERE dummy = 'missing value'` actually return a row? If not, it will always result in zero rows inserted and therefore no FK violation. But your MySQL version may have a bug in the affected rows output. I tried it on 5.7 and 5.6.35, but both mine reported zero rows inserted rather than 1. — Michael Berkowski, Dec 23 '16 at 00:30
You're correct. I was running the SQL queries inside IntelliJ and it reported the result `1 row affected in 5ms`. I ran the same queries from the MySQL command line, and received `Query OK, 0 rows affected (0.01 sec)`. The question remains, why does inserting as select in this format not raise the NOT NULL constraint violation, whereas in other formats it does? — adam, Dec 23 '16 at 00:35
Because it never even attempts an insert if there's no row from the `SELECT`. It's different from your 3rd example, where the literal `123` forces a row insert along with a `NULL` returned from the subselect. If you tried something like `INSERT INTO Test_Child (fid) SELECT NULL FROM Test_Parent` where the select does return a row with a `NULL` value, you should get a FK violation. — Michael Berkowski, Dec 23 '16 at 00:41
That seems reasonable. Thanks for your help @MichaelBerkowski — adam, Dec 23 '16 at 01:01

score 1 · Accepted Answer · answered Dec 23 '16 at 01:00

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

MySQL Insert Select Doesn't Enforce NOT NULL Constraint

1 Answers1