MySQL无法创建外键约束

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

我有桌子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使用以下内容创建一个名为referencing this 的新表:

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

为了获得更多信息,我做了:

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列设置为非null,但这并没有解决它)所以我想问题必须是这样Cannot find an index in the referenced table where the referenced columns appear as the first columns.但我不太清楚这意味着什么,或者如何检查/修复它.有没有人有什么建议？这是什么意思first columns？

解:

我想出了我的问题.我对exp表(utf8_unicode_ci)有一个不同的排序规则,然后是数据库的默认排序(utf8_general_ci).所以加入

+-------------+------------------+------+-----+---------+-------+
| 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    |       |
+-------------+------------------+------+-----+---------+-------+

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

但我仍然对这是什么意思感到好奇exp.它与索引有关吗？