Description:
A quote from https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html :
InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
The restriction above is NOT enforced in some cases (see examples under the "How to repeat" section).
How to repeat:
-- Example 1 - indexes on columns a and b are not unique.
DROP TABLE IF EXISTS tbl2;
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1(a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB;
CREATE INDEX a_idx ON tbl1(a);
CREATE INDEX b_idx ON tbl1(b);
CREATE TABLE tbl2(aa INT NOT NULL, bb INT NOT NULL, CONSTRAINT fk FOREIGN KEY(aa, bb) REFERENCES tbl1(a, b)) ENGINE=InnoDB;
-- SQL Error (1215): Cannot add foreign key constraint
-- Correct! Table tbl1 does not have a composite index for columns (a, b).
-- Same as 1st example but both indexes are unique.
DROP TABLE IF EXISTS tbl2;
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1(a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB;
CREATE UNIQUE INDEX a_idx ON tbl1(a);
CREATE UNIQUE INDEX b_idx ON tbl1(b);
CREATE TABLE tbl2(aa INT NOT NULL, bb INT NOT NULL, CONSTRAINT fk FOREIGN KEY(aa, bb) REFERENCES tbl1(a, b)) ENGINE=InnoDB;
-- SQL Error (1215): Cannot add foreign key constraint
-- Correct! Table tbl1 does not have a composite index for columns (a, b).
-- Same as 1st example but only index on column a is unique.
DROP TABLE IF EXISTS tbl2;
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1(a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB;
CREATE UNIQUE INDEX a_idx ON tbl1(a);
CREATE INDEX b_idx ON tbl1(b);
CREATE TABLE tbl2(aa INT NOT NULL, bb INT NOT NULL, CONSTRAINT fk FOREIGN KEY(aa, bb) REFERENCES tbl1(a, b)) ENGINE=InnoDB;
-- SQL Error (1215): Cannot add foreign key constraint
-- Correct! Table tbl1 does not have a composite index for columns (a, b).
-- Same as 1st example but only index on column b is unique.
DROP TABLE IF EXISTS tbl2;
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1(a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB;
CREATE INDEX a_idx ON tbl1(a);
CREATE UNIQUE INDEX b_idx ON tbl1(b);
CREATE TABLE tbl2(aa INT NOT NULL, bb INT NOT NULL, CONSTRAINT fk FOREIGN KEY(aa, bb) REFERENCES tbl1(a, b)) ENGINE=InnoDB;
-- Table tbl1 still does not have a composite index for columns (a, b).
-- Yet, there are no errors and the table tbl2 with foreign key constraint is created successfully!
-- This is wrong - should have been the same error as in previous examples according to the restriction quoted from documentation above.
Suggested fix:
MySQL should not allow creation of foreign key constraints that do not follow documented restriction of having proper index in parent table and return error for last example.
