Description:
Expected:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
Actual: the INSERT succeeds and this row is stored, although no row with key (5, 777) exists.
k1=5 k2=100 f1=5 f2=777
Both foreign key columns are non-NULL, so the documented MATCH SIMPLE case - which permits a key that
is all or partially NULL - does not apply.
innodb_native_foreign_keys=OFF (default, SQL layer): accepted, the row is stored
innodb_native_foreign_keys=ON (InnoDB): ERROR 1452
MySQL 8.0.28: ERROR 1452
Trigger, all three together; change any one and the constraint is enforced again:
1. the foreign key is composite AND self-referencing;
2. the table is empty when the row is inserted;
3. the new row's foreign key leading column equals its own key leading column (f1 = k1).
The catalogue still reports the constraint and no DDL statement fails, so no catalogue-reading check
can see this; only an oracle that tries to violate the constraint can.
How to repeat:
1. Run the SQL below on 26.7.0 with default settings.
2. Observe that the first INSERT is accepted and the row is stored, although (5, 777) exists nowhere.
3. The control INSERT differs only in f1 and is refused with ERROR 1452.
4. Restart with --innodb_native_foreign_keys=ON and run the first INSERT again: now ERROR 1452.
DROP DATABASE IF EXISTS bug2;
CREATE DATABASE bug2;
USE bug2;
CREATE TABLE t (k1 BIGINT NOT NULL, k2 BIGINT NOT NULL,
f1 BIGINT NULL, f2 BIGINT NULL,
PRIMARY KEY (k1, k2),
CONSTRAINT fk_t FOREIGN KEY (f1, f2) REFERENCES t (k1, k2)) ENGINE=InnoDB;
-- the table is empty; no row with key (5, 777) exists
INSERT INTO t (k1, k2, f1, f2) VALUES (5, 100, 5, 777);
-- accepted on the default SQL-layer path
-- control: same shape, f1 differs from the row's own k1
DELETE FROM t;
INSERT INTO t (k1, k2, f1, f2) VALUES (5, 100, 6, 777);
-- ERROR 1452
© 2026, Oracle Corporation and/or its affiliates
Description: Expected: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails Actual: the INSERT succeeds and this row is stored, although no row with key (5, 777) exists. k1=5 k2=100 f1=5 f2=777 Both foreign key columns are non-NULL, so the documented MATCH SIMPLE case - which permits a key that is all or partially NULL - does not apply. innodb_native_foreign_keys=OFF (default, SQL layer): accepted, the row is stored innodb_native_foreign_keys=ON (InnoDB): ERROR 1452 MySQL 8.0.28: ERROR 1452 Trigger, all three together; change any one and the constraint is enforced again: 1. the foreign key is composite AND self-referencing; 2. the table is empty when the row is inserted; 3. the new row's foreign key leading column equals its own key leading column (f1 = k1). The catalogue still reports the constraint and no DDL statement fails, so no catalogue-reading check can see this; only an oracle that tries to violate the constraint can. How to repeat: 1. Run the SQL below on 26.7.0 with default settings. 2. Observe that the first INSERT is accepted and the row is stored, although (5, 777) exists nowhere. 3. The control INSERT differs only in f1 and is refused with ERROR 1452. 4. Restart with --innodb_native_foreign_keys=ON and run the first INSERT again: now ERROR 1452. DROP DATABASE IF EXISTS bug2; CREATE DATABASE bug2; USE bug2; CREATE TABLE t (k1 BIGINT NOT NULL, k2 BIGINT NOT NULL, f1 BIGINT NULL, f2 BIGINT NULL, PRIMARY KEY (k1, k2), CONSTRAINT fk_t FOREIGN KEY (f1, f2) REFERENCES t (k1, k2)) ENGINE=InnoDB; -- the table is empty; no row with key (5, 777) exists INSERT INTO t (k1, k2, f1, f2) VALUES (5, 100, 5, 777); -- accepted on the default SQL-layer path -- control: same shape, f1 differs from the row's own k1 DELETE FROM t; INSERT INTO t (k1, k2, f1, f2) VALUES (5, 100, 6, 777); -- ERROR 1452