------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
它说问题是找不到索引。 SHOW INDEX FROM t1 显示表 根本没有任何索引 t1 。通过在 上定义主键来修复该问题 t1 ,外键约束将成功创建。
当然不是,但我发现这个错误很常见且不明显。 a 的目标 FOREIGN KEY 可能不是 PRIMARY KEY 。对我有用的答案是:
FOREIGN KEY 必须始终指向其他表的 PRIMARY KEY 真实字段。
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40));
CREATE TABLE userroles(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id));
有用的提示, SHOW WARNINGS; 尝试 CREATE 查询后使用,您将收到错误以及更详细的警告:
---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
| Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table 'exampleTable' (errno:150) |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+