MySQL 无法创建外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21526055/
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 cannot create foreign key constraint
提问by numfar
I'm having some problems creating a foreign key to an existing table in a mysql database.
我在为 mysql 数据库中的现有表创建外键时遇到了一些问题。
I have the table exp:
我有桌子exp：
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| EID | varchar(45) | NO | PRI | NULL | |
| Comment | text | YES | | NULL | |
| Initials | varchar(255) | NO | | NULL | |
| ExpDate | date | NO | | NULL | |
| InsertDate | date | NO | | NULL | |
| inserted_by | int(11) unsigned | YES | MUL | NULL | |
+-------------+------------------+------+-----+---------+-------+
and I wan't to create a new table called sample_dfreferencing this, using the following:
我不想创建一个名为sample_df引用这个的新表，使用以下内容：
CREATE TABLE sample_df (
df_id mediumint(5) unsigned AUTO_INCREMENT primary key,
sample_type mediumint(5) unsigned NOT NULL,
df_10 BOOLEAN NOT NULL,
df_100 BOOLEAN NOT NULL,
df_1000 BOOLEAN NOT NULL,
df_above_1000 BOOLEAN NOT NULL,
target INT(11) unsigned NOT NULL,
assay MEDIUMINT(5) unsigned zerofill NOT NULL,
insert_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by INT(11) unsigned NOT NULL,
initials varchar(255),
experiment VARCHAR(45),
CONSTRAINT FOREIGN KEY (inserted_by) REFERENCES user (iduser),
CONSTRAINT FOREIGN KEY (target) REFERENCES protein (PID),
CONSTRAINT FOREIGN KEY (sample_type) REFERENCES sample_type (ID),
CONSTRAINT FOREIGN KEY (assay) REFERENCES assays (AID),
CONSTRAINT FOREIGN KEY (experiment) REFERENCES exp (EID)
);
But I get the error:
但我收到错误：
ERROR 1215 (HY000): Cannot add foreign key constraint
To get some more information I did:
为了获得更多信息，我做了：
SHOW ENGINE INNODB STATUS\G
From which I got:
我从中得到：
FOREIGN KEY (experiment) REFERENCES exp (EID)
):
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.
To me the column types seem to match, since they are both varchar(45).(I also tried setting the experimentcolumn to not null, but this didn't fix it) So I guess the problem must be that Cannot find an index in the referenced table where the referenced columns appear as the first columns. But I'm not quite sure what this means, or how to check/fix it. Does anyone have any suggestions? And what is meant by first columns?
对我来说，列类型似乎匹配，因为它们都是 varchar(45)。（我也尝试将experiment列设置为非空，但这并没有解决它）所以我想问题一定是Cannot find an index in the referenced table where the referenced columns appear as the first columns. 但我不太确定这意味着什么，或者如何检查/修复它。有没有人有什么建议？是什么意思first columns？
回答by Austen Hoogen
Just throwing this into the mix of possible causes, I ran into this when the referencing table column had the same "type" but did not have the same signing.
只是将其放入可能的原因组合中，当引用表列具有相同的“类型”但没有相同的签名时，我遇到了这个问题。
In my case, the referenced table colum was TINYINT UNSIGNED and my referencing table column was TINYINT SIGNED. Aligning both columns solved the issue.
就我而言，引用的表列是 TINYINT UNSIGNED，而我的引用表列是 TINYINT SIGNED。对齐两列解决了这个问题。
回答by Pratik Singhal
This error can also occur, if the references table and the current table don't have the same character set.
如果引用表和当前表没有相同的字符集，也会发生此错误。
回答by Anton
According to http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html
根据http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
InnoDB permits a foreign key to reference any index 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.
MySQL 需要外键和引用键的索引，以便外键检查可以快速并且不需要表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列。
InnoDB 允许外键引用任何索引列或列组。但是，在被引用的表中，必须有一个索引，其中被引用的列以相同的顺序列为第一列。
So if the index in referenced table is exist and it is consists from several columns, and desired column is not first, the error shall be occurred.
因此，如果被引用表中的索引存在并且由多列组成，并且所需的列不是第一个，就会发生错误。
The cause of our error was due to violation of following rule:
我们错误的原因是由于违反了以下规则：
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.
外键和引用键中对应的列必须具有相似的数据类型。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。
回答by antongorodezkiy
As mentioned @Anton, this could be because of the different data type. In my case I had primary key BIGINT(20) and tried to set foreight key with INT(10)
正如@Anton 所提到的，这可能是因为数据类型不同。就我而言，我有主键 BIGINT(20) 并尝试使用 INT(10) 设置前八键
回答by Nick Manning
In my case, it turned out the referenced column wasn't declared primary or unique.
就我而言，事实证明引用的列未声明为主要的或唯一的。
回答by user11891461
Mine was a collation issue between the referenced table and the to be created table so I had to explicitly set the collation type of the key I was referencing.
我的是引用表和要创建的表之间的排序规则问题，所以我必须明确设置我引用的键的排序规则类型。
- First I ran a query at referenced table to get its collation type
- 首先，我在引用的表上运行查询以获取其整理类型
show table STATUS like '<table_name_here>';
- Then I copied the collation type and explicitly stated employee_id's collation type at the creation query. In my case it was utf8_general_ci
- 然后我复制了排序规则类型并在创建查询中明确说明了employee_id 的排序规则类型。就我而言，它是utf8_general_ci
CREATE TABLE dbo.sample_db
(
id INT PRIMARY KEY AUTO_INCREMENT,
event_id INT SIGNED NOT NULL,
employee_id varchar(45) COLLATE utf8_general_ci NOT NULL,
event_date_time DATETIME,
CONSTRAINT sample_db_event_event_id_fk FOREIGN KEY (event_id) REFERENCES event (event_id),
CONSTRAINT sample_db_employee_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
);
回答by sprksh
In some cases, I had to make the referenced field unique on top of defining it as the primary key.
在某些情况下，除了将引用的字段定义为主键之外，我还必须使其唯一。
But I found that not defining it as unique doesn't create a problem in every case. I have not been able to figure out the scenarios though. Probably something to do with nullable definition.
但我发现不将其定义为唯一性并不会在每种情况下都会产生问题。我一直无法弄清楚场景。可能与可为空定义有关。
回答by Nelson
The exactorder of the primary key also needs to match with no extra columns in between.
主键的确切顺序也需要匹配，中间没有额外的列。
I had a primary key setup where the column orderactually matches, but the problem was the primary key had an extra column in it that is not part of the foreign key of the referencing table
我有一个主键设置，其中列顺序实际上匹配，但问题是主键中有一个额外的列，它不是引用表的外键的一部分
e.g.) table 2, column (a, b, c) -> table 1, column (a, b, d, c) -- THIS FAILS
例如）表 2，列 (a, b, c) -> 表 1，列 (a, b, d, c) -- 这失败了
I had to reorder the primary key columns so that not only they're ordered the same way, but have no extra columns in the middle:
我不得不重新排序主键列，这样不仅它们的排序方式相同，而且中间没有额外的列：
e.g.) table 2, column (a, b, c) -> table 1, column (a, b, c, d) -- THIS SUCCEEDS
例如）表 2，列（a，b，c）-> 表 1，列（a，b，c，d）——这成功了
回答by Paul Carlton
I had this error as well. None of the answers pertained to me. In my case, my GUI automatically creates a table with a primary unique identifier as "unassigned". This fails when I try and create a foreign key and gives me the exact same error. My primary key needs to be assigned.
我也有这个错误。没有一个答案与我有关。就我而言，我的 GUI 会自动创建一个表，其主要唯一标识符为“未分配”。当我尝试创建外键并给我完全相同的错误时，这失败了。我的主键需要分配。
If you write the SQL itself like so id int unique auto_incrementthen you don't have this issue but for some reason my GUI does this instead id int unassigned unique auto_increment.
如果您像这样编写 SQL 本身，id int unique auto_increment那么您就没有这个问题，但由于某种原因，我的 GUI 会这样做id int unassigned unique auto_increment。
Hope this helps someone else down the road.
希望这可以帮助其他人。
回答by helenatxu
In my case was created using integerfor the id, and the referencing table was creating by default a foreign key using bigint.
在我的例子中，id使用整数创建，引用表默认使用bigint创建外键。
This caused a big nightmare in my Rails app as the migration failed but the fields were actually created in DB, so they showed up in the DB but not in the schema of the Rails app.
这在我的 Rails 应用程序中造成了一场噩梦，因为迁移失败了，但这些字段实际上是在数据库中创建的，所以它们出现在数据库中，但没有出现在 Rails 应用程序的架构中。
