MySql 错误 150 - 外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/825362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Error 150 - Foreign keys
提问by Martin Thurau
When I execute the follow two queries (I have stripped them down to absolutely necessary):
当我执行以下两个查询时（我已将它们精简到绝对必要）：
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;
I get the following error: ERROR 1005 (HY000): Can't create table './test/bar.frm' (errno: 150)
我收到以下错误：ERROR 1005 (HY000): Can't create table './test/bar.frm' (errno: 150)
Where the **** is my error? I haven't found him while staring at this for half an hour.
**** 哪里是我的错误？我盯着这个看了半个小时都没找到他。
回答by Greg
If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.
如果您重新创建一个被删除的表，它必须有一个符合引用它的外键约束的定义。如前所述，它必须具有正确的列名和类型，并且必须在所引用的键上具有索引。如果这些都不满足，MySQL 将返回错误号 1005 并在错误消息中引用错误 150。
My suspicionis that it's because you didn't create fooas InnoDB, as everything else looks OK.
我怀疑这是因为您没有创建fooInnoDB，因为其他一切看起来都不错。
Edit: from the same page -
编辑：从同一页面 -
Both tables must be InnoDB tables and they must not be TEMPORARY tables.
两个表都必须是 InnoDB 表并且它们不能是 TEMPORARY 表。
回答by user2906840
You can use the command SHOW ENGINE INNODB STATUSto get more specific information about the error.
您可以使用该命令SHOW ENGINE INNODB STATUS来获取有关错误的更多具体信息。
It will give you a result with a Statuscolumn containing a lot of text.
它会给你一个Status包含大量文本的列的结果。
Look for the section called LATEST FOREIGN KEY ERROR which could for example look like this:
查找名为 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'.
回答by Suyash Jain
To create a foreign key ,
要创建外键，
- both the main column and the reference column must have same definition.
- both tables engine must be InnoDB.
- 主列和参考列必须具有相同的定义。
- 两个表引擎都必须是 InnoDB。
You can alter the engine of table using this command , please take the backup before executing this command.
您可以使用此命令更改表的引擎，请在执行此命令前做好备份。
alter table [table name] ENGINE=InnoDB;
更改表 [表名] ENGINE=InnoDB;
回答by Madhab Chandra Pal
Apart form many other reasons to end up with MySql Error 150 (while using InnoDB), One of the probable reason, is the undefined KEYin the create statement of the table containing the column name referenced as a foreign key in the relative table.
除了以 MySql 错误 150 结束的许多其他原因（使用 InnoDB 时），可能的原因KEY之一是表的 create 语句中的undefined包含在相关表中作为外键引用的列名。
Let's say the create statement of master table is -
假设主表的创建语句是 -
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 '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;
and the create syntax for the relative_table table where the foreign key constraint is set from primary table -
以及从主表设置外键约束的 relative_table 表的创建语法 -
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;
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;
This script is definitely going to end with MySql Error 150 if using InnoDB.
如果使用 InnoDB，这个脚本肯定会以 MySql 错误 150 结束。
To solve this, we need to add a KEYfor the The column record_idin the master_tabletable and then reference in the relative_tabletable to be used as a foreign_key.
为了解决这个问题，我们需要添加KEY的列record_id在master_table表中，然后在引用relative_table表用作一个foreign_key。
Finally, the create statement for the master_table, will be -
最后， , 的 create 语句master_table将是 -
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'),
KEY 'record_id' ('record_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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'),
KEY 'record_id' ('record_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
回答by webmaster
I had the same problem, for those who are having this also:
我遇到了同样的问题，对于那些也有这个问题的人：
check the table name of the referenced table
检查引用表的表名
I had forgotten the 's' at the end of my table name
我忘记了表名末尾的“s”
eg table Client --> Clients
例如表客户端 --> 客户端
:)
:)
回答by Hamed J.I
I had very same problem and the reason was the "collation" of columns was different. One was latin1 while the other was utf8
我有同样的问题，原因是列的“整理”不同。一个是 latin1 而另一个是 utf8
回答by Ajay Sharma
This may also happen if you have not given correct column name after "references" keyword.
如果您没有在“references”关键字后给出正确的列名，也可能会发生这种情况。
