我在创建一个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
,它将引用此内容并使用以下代码:
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):无法添加外键约束
为了获取更多信息,我执行了以下操作:
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
,但这并没有解决问题)。所以我猜问题一定是:但我不太确定这意味着什么,或者如何检查/修复它。有人有什么建议吗?而且什么是第一列的含义?在参考表中找不到索引,其中被参考的列出现在第一列。