Description:
On MySQL 26.7.0 with default @@innodb_native_foreign_keys=OFF, a FK check is silently skipped when the supporting non-unique multi-column index contains a NULL in a non-FK column, allowing a non-NULL dangling FK value to be inserted.
In the test case (see "How to repeat"), index i is on (f1, other), but the FK is only on f1. Inserting (id=1, f1=999, other=NULL) succeeds, storing a dangling reference to a non-existent parent row. The FK column f1 itself is non-NULL and invalid.
This is a regression: it does not occur on 8.0.28, nor on 26.7.0 with --innodb_native_foreign_keys=TRUE. Re-declaring the FK does not fix it; the index must be dropped. The constraint remains visible in the data dictionary (TABLE_CONSTRAINTS, INNODB_FOREIGN, etc.), making this silent data corruption hard to detect.
Expected behavior: ERROR 1452.
Workaround: start the server with --innodb_native_foreign_keys=TRUE.
This is not in the documented list of deviations and contradicts the stated backward compatibility of the 9.6 change. Is this intended?
How to repeat:
CREATE DATABASE fkbug;
USE fkbug;
CREATE TABLE p (k INT NOT NULL, PRIMARY KEY (k));
CREATE TABLE c (id INT NOT NULL, f1 INT, other INT, PRIMARY KEY (id),
CONSTRAINT c1 FOREIGN KEY (f1) REFERENCES p (k));
INSERT INTO p VALUES (1); -- the parent table holds exactly one row: k = 1
-- no index yet: the dangling value is rejected, as expected
INSERT INTO c VALUES (1, 999, 7); -- ERROR 1452 (23000)
CREATE INDEX i ON c (f1, other); -- non-unique, leading column is the foreign key column
-- still rejected, because "other" is not NULL
INSERT INTO c VALUES (2, 999, 7); -- ERROR 1452 (23000)
-- accepted, although f1 = 999 has no parent row
INSERT INTO c VALUES (3, 999, NULL);
SELECT * FROM c; -- 3 999 NULL
