MySQL无法创建外键约束 [英] MySQL cannot create foreign key constraint

问题描述

我在为mysql数据库中的现有表创建外键时遇到一些问题。

I'm having some problems creating a foreign key to an existing table in a mysql database.

我有表 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    |       |
+-------------+------------------+------+-----+---------+-------+

和我不要使用以下内容创建一个名为 sample_df 的新表来引用它：

and I wan't to create a new table called sample_df referencing this, using the following:

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)
);

但我收到错误：

ERROR 1215 (HY000): Cannot add foreign key constraint

至得到更多我做的信息：

To get some more information I did:

SHOW ENGINE INNODB STATUS\G

我从中得到：

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.

对我来说，列类型似乎匹配，因为它们都是varchar（45）。（我也是尝试将 experiment 列设置为not null，但是这并没有修复它。所以我想问题必须是无法找到索引引用的列，其中引用的列显示为第一列。但我不太清楚这意味着什么，或者如何检查/修复它。有没有人有什么建议？什么是第一列？

To me the column types seem to match, since they are both varchar(45).(I also tried setting the experiment column 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?

解决方案：

我发现了我的问题。我对exp表（utf8_unicode_ci）有一个不同的排序规则，然后是数据库的默认排序（utf8_general_ci）。所以添加

I figured out my problem. I had a different collation for the exp table (utf8_unicode_ci) then the default for the database(utf8_general_ci). So adding

CHARACTER SET utf8 COLLATE utf8_unicode_ci

在表创建查询结束时修复了我的问题。

at the end of the table creation query fixed my problem.

但我仍然对<$的含义感到好奇c $ c>第一列。它与索引有关吗？

But I'm still curious about what is meant by first columns. Is it something to do with indexes?

推荐答案

只是把它扔进可能原因的混合中，我在引用时遇到了这个问题table列具有相同的类型，但没有相同的签名。

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.

在我的情况下，引用的表colum是TINYINT UNSIGNED，我的引用表列是TINYINT SIGNED 。对齐两列都解决了这个问题。

In my case, the referenced table colum was TINYINT UNSIGNED and my referencing table column was TINYINT SIGNED. Aligning both columns solved the issue.

这篇关于MySQL无法创建外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！