MySql错误150 - 外键

Question

当我执行以下两个查询时（已经删减到绝对必要的内容）：

mysql> CREATE TABLE foo(id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE bar ( id INT, ref INT, FOREIGN KEY (ref) REFERENCES foo(id)) ENGINE InnoDB;

我遇到了以下错误：

ERROR 1005 (HY000): 无法创建'./test/bar.frm'表（错误编号：150）

我的错误到底出在哪里？我已经盯着这个问题半个小时了，还是没找到。

- Martin Thurau

Answer 1

来自FOREIGN KEY约束

如果您重新创建一个已删除的表，则该表必须具有符合引用它的外键约束的定义。 它必须具有正确的列名称和类型，并且必须在引用键上具有索引，如前所述。 如果这些条件不满足，MySQL将返回错误编号1005，并引用错误消息中的错误150。

我怀疑问题是因为您没有将foo创建为InnoDB表，其他看起来都没问题。

编辑：来自同一页面-

两个表都必须是InnoDB表，且不能是TEMPORARY表。

- Greg

---

进行一些快速测试，使用MyISAM创建一个foo，并使用InnoDB创建一个bar，这表明情况确实如此 - 请仔细检查foo的表类型。 - Rob

即使两个表都是InnoDB，我仍然遇到了同样的问题。在创建外键之前，在被引用的表上创建索引解决了这个问题。 - IROEGBU

MySQL Workbench 6.3 似乎会在我创建新表时随机将引擎类型从 InnoDB 更改为 MyISAM，这并没有帮助到我。 - Chris Peacock

Answer 2

您可以使用命令SHOW ENGINE INNODB STATUS来获取有关错误的更具体信息。

它将给您一个包含大量文本的Status列的结果。

查找名为“LATEST FOREIGN KEY ERROR”的部分，示例可能如下：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
190215 11:51:26 Error in foreign key constraint of table `mydb1`.`contacts`:
Create  table `mydb1`.`contacts` with foreign key constraint failed. You have defined a SET NULL condition but column 'domain_id' is defined as NOT NULL in ' FOREIGN KEY (domain_id) REFERENCES domains (id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT contacts_teams_id_fk FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT' near ' ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT contacts_teams_id_fk FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT'.

- user2906840

---

同意！它显示了一个名为“最后外键错误”的部分，对于我的情况，它告诉我一个外键约束的目标字段名称未被找到。这比单纯的“错误150”更有帮助;-) - Marek Stanley

在我的情况下，我忘记将列设置为允许NULL，并将SET NULL用于约束。但是我只能通过运行SHOW ENGINE INNODB STATUS找出原因。 - Robert K

这应该是最佳答案！ 尽管在此查询的“状态”列输出中有一个“最新外键错误”部分可能会有所帮助，如@MarekStanley也清楚地指出的那样。 - Tommy Bravo

Answer 3

创建外键需要满足以下两个条件：

1. 主列和引用列必须具有相同的定义。
2. 两个表的引擎必须为InnoDB。

您可以使用以下命令更改表的引擎，请在执行此命令之前备份。

alter table [table name] ENGINE=InnoDB;

- Suyash Jain

---

“must have same definition” 对我来说意思是需要使用相同的字符编码。 - gengisdave

“必须具有相同的定义”对我来说意味着在同一列标志下: 我参考的现有主键是UNSIGNED类型，因此我需要将我的新表的列也设置为FOREIGN KEY以便与第一个表建立关联。 - cellepo

Answer 4

我曾经遇到过同样的问题，对于那些也遇到这个问题的人：

检查所引用表格的名称

我忘记了我的表格名称末尾的 's'

例如：表格 Client --> Clients

:)

- webmaster

Answer 5

除了许多其他原因导致使用InnoDB时出现MySql错误150之外，可能的一个原因是在包含作为相对表中外键引用的列名的表的创建语句中未定义“KEY”。

假设主表的创建语句如下：

CREATE TABLE 'master_table' (  'id' int(10) NOT NULL AUTO_INCREMENT,  'record_id' char(10) NOT NULL,  'name' varchar(50) NOT NULL DEFAULT '',  'address' varchar(200) NOT NULL DEFAULT '',  PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

相对表的创建语法如下，其中设置了外键约束：

CREATE TABLE 'relative_table' (
 'id' int(10) NOT NULL AUTO_INCREMENT,
 'salary' int(10) NOT NULL DEFAULT '',
 'grade' char(2) NOT NULL DEFAULT '',
 'record_id' char(10) DEFAULT NULL,
 PRIMARY KEY ('id'),
 CONSTRAINT 'fk_slave_master' FOREIGN KEY ('record_id') REFERENCES 'master' ('record_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果使用InnoDB，则此脚本肯定会以MySql Error 150结束。

为解决此问题，我们需要在master_table表中的列record_id中添加一个KEY，然后在relative_table表中引用它作为外键。

最终，master_table的创建语句将为-

创建'master_table'表(
 'id' 整型(10) 非空 自动增量,
 'record_id' 字符型(10) 非空,
 'name' 变长字符型(50) 非空 默认值为空字符串,
 'address' 变长字符型(200) 非空 默认值为空字符串,
 主键为'id',
 索引 'record_id' ('record_id')
) 引擎为InnoDB 默认字符集为utf8;

- Madhab Chandra Pal

Answer 6

我曾经遇到过同样的问题，原因是列的“排序规则”不同。其中一个是latin1，而另一个是utf8。

- Hamed J.I

Answer 7

这可能也会发生在您在“references”关键字后没有给出正确的列名的情况下。

- Ajay Sharma