30accepted
When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn't exist, but the one on the referenced table needs to be created manually (
Source). Yours appears to be missing.
Test case:
CREATE TABLE tbl_a ( id int PRIMARY KEY, some_other_id int, value int) ENGINE=INNODB;Query OK, 0 rows affected (0.10 sec)CREATE TABLE tbl_b ( id int PRIMARY KEY, a_id int, FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)) ENGINE=INNODB;ERROR 1005 (HY000): Can't create table 'e.tbl_b' (errno: 150)But if we add an index on some_other_id:
CREATE INDEX ix_some_id ON tbl_a (some_other_id);Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0CREATE TABLE tbl_b ( id int PRIMARY KEY, a_id int, FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)) ENGINE=INNODB;Query OK, 0 rows affected (0.06 sec)This is often not an issue in most situations, since the referenced field is often the primary key of the referenced table, and the primary key is indexed automatically.
本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報。
