MySQL - 错误代码 1215，无法添加外键约束

MySQL - Error Code 1215, cannot add foreign key constraint

提问by user3487194

i got these two succesfull queries:

我得到了这两个成功的查询：

create table Donors (
    donor_id int not null auto_increment primary key,
    gender varchar(1) not null,
    date_of_birth date not null,
    first_name varchar(20) not null,
    middle_name varchar(20),
    last_name varchar(30) not null,
    home_phone tinyint(10),
    work_phone tinyint(10),
    cell_mobile_phone tinyint(10),
    medical_condition text,
    other_details text );

and

和

create table Donors_Medical_Condition (
    donor_id int not null,
    condition_code int not null,
    seriousness text,
    primary key(donor_id, condition_code),
    foreign key(donor_id) references Donors(donor_id)    );

but when i try this one:

但是当我尝试这个时：

create table Medical_Conditions (
    condition_code int not null,
    condition_name varchar(50) not null,
    condition_description text,
    other_details text,
    primary key(condition_code),
    foreign key(condition_code) references Donors_Medical_Condition(condition_code) );

i get "Error Code: 1215, cannot add foreign key constraint"

我收到“错误代码：1215，无法添加外键约束”

i dont know what am i doing wrong.

我不知道我做错了什么。

回答by Gary Walker

In MySql, a foreign key reference needs to reference to an index (including primary key), where the first part of the index matches the foreign key field. If you create an an index on condition_code or change the primary key st that condition_code is first you should be able to create the index.

在MySql中，外键引用需要引用一个索引（包括主键），其中索引的第一部分匹配外键字段。如果您在 condition_code 上创建索引或更改 condition_code 首先的主键 st，您应该能够创建索引。

回答by Ravinder Reddy

To define a foreign key, the referenced parent field must have an index defined on it.

要定义 a foreign key，引用的父字段必须在其上定义索引。

As per documentation on foreign keyconstraints:

根据有关foreign key约束的文档：

REFERENCES tbl_name (index_col_name,...)

参考 tbl_name (index_col_name,...)

Define an INDEXon condition_codein parent table Donors_Medical_Conditionand it should be working.

在父表中定义一个INDEXon condition_code，Donors_Medical_Condition它应该可以工作。

create table Donors_Medical_Condition (
    donor_id int not null,
    condition_code int not null,
    seriousness text,

    KEY ( condition_code ), -- <---- this is newly added index key

    primary key(donor_id, condition_code),
    foreign key(donor_id) references Donors(donor_id)    );

But it seems you defined your tables order and references wrongly. You should have defined foreign keyin Donors_Medical_Conditiontable but not in Donors_Medical_Conditionstable. The latter seems to be a parent.

但是您似乎错误地定义了表格顺序和引用。您应该foreign key在Donors_Medical_Condition表中定义但不在Donors_Medical_Conditions表中定义。后者似乎是父母。

Modify your script accordingly.

相应地修改您的脚本。

They should be written as:

它们应该写成：

-- create parent table first ( general practice )
create table Medical_Conditions (
    condition_code int not null,
    condition_name varchar(50) not null,
    condition_description text,
    other_details text,
    primary key(condition_code)
);

-- child table of Medical_Conditions 
create table Donors_Medical_Condition (
    donor_id int not null,
    condition_code int not null,
    seriousness text,
    primary key(donor_id, condition_code),
    foreign key(donor_id) references Donors(donor_id),
    foreign key(condition_code) 
        references Donors_Medical_Condition(condition_code)
);

Refer to:

参考：

• MySQL Using FOREIGN KEY Constraints

• MySQL 使用 FOREIGN KEY 约束

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

参考选项：
限制 | 级联 | 置空 | 没有行动

回答by Heather

A workaroundfor those who need a quick how-to:
FYI: My issue was NOT caused by the inconsistency of the columns' data types/sizes, collation or InnoDB storage engine.

对于需要快速 入门的人的解决方法：
仅供参考：我的问题不是由列的数据类型/大小、排序规则或 InnoDB 存储引擎的不一致引起的。

How to:
Download a MySQL workbench and use it's GUI to add foreign key. That's it!

如何：
下载 MySQL 工作台并使用它的 GUI 添加外键。就是这样！

Why:
The error DOES have something to do with indexes. I learned this from the DML script automatically generated by the MySQL workbench. Which also helped me to rule out all those inconsistency possibilities.It applies to one of the conditions to which the foreign key definition subject. That is: “MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.” Here is the official statement: http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
I did not get the idea of adding an index ON the foreign key column(in the child table), only paid attention to the referenced TO column(in the parent table).
Here is the auto-generated script(PHONE.PERSON_ID did not have index originally):

为什么：
错误确实与索引有关。我从 MySQL 工作台自动生成的 DML 脚本中了解到这一点。这也帮助我排除了所有那些不一致的可能性。它适用于外键定义主题的条件之一。那就是：“MySQL 需要外键和引用键上的索引，以便外键检查可以快速并且不需要表扫描。” 这是官方声明：http: //dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
我没有想到在外键列上添加索引（在子表），只关注引用的 TO 列（在父表中）。
这是自动生成的脚本（PHONE.PERSON_ID 最初没有索引）：

ALTER TABLE `netctoss`.`phone` 
ADD INDEX `personfk_idx` (`PERSON_ID` ASC);
ALTER TABLE `netctoss`.`phone` 
ADD CONSTRAINT `personfk`
  FOREIGN KEY (`PERSON_ID`)
  REFERENCES `netctoss`.`person` (`ID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

回答by Vijay Kumar Rajput

I got the same issue and as per given answers, I verified all datatype and reference but every time I recreate my tables I get this error. After spending couple of hours I came to know below command which gave me inside of error-

我遇到了同样的问题，根据给定的答案，我验证了所有数据类型和引用，但每次重新创建表时都会出现此错误。花了几个小时后，我开始了解下面的命令，这给了我错误信息-

SHOW ENGINE INNODB STATUS;

显示引擎 INNODB 状态；

LATEST FOREIGN KEY ERROR
------------------------
2015-05-16 00:55:24 12af3b000 Error in foreign key constraint of table letmecall/lmc_service_result_ext:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "fk_SERVICE_RESULT_EXT_LMC_SERVICE_RESULT1" FOREIGN KEY ("FK_SERVICE_RESULT") REFERENCES "LMC_SERVICE_RESULT" ("SERVICE_RESULT") ON DELETE NO ACTION ON UPDATE NO ACTION

I removed all relation using mysql workbench but still I see same error. After spending few more minutes, I execute below statement to see all constraint available in DB-

我使用 mysql workbench 删除了所有关系，但仍然看到相同的错误。多花几分钟后，我执行以下语句以查看 DB 中可用的所有约束-

select * from information_schema.table_constraints where constraint_schema = 'XXXXX'

select * from information_schema.table_constraints where constraint_schema = 'XXXXX'

I was wondering that I have removed all relationship using mysql workbench but still that constraint was there. And the reason was that because this constraint was already created in db.

我想知道我已经使用 mysql workbench 删除了所有关系，但仍然存在该约束。原因是因为这个约束已经在 db 中创建了。

Since it was my test DB So I dropped DB and when I recreate all table along with this table then it worked. So solution was that this constraint must be deleted from DB before creating new tables.

因为它是我的测试数据库所以我删除了数据库，当我重新创建所有表和这个表时，它就起作用了。所以解决方案是在创建新表之前必须从数据库中删除这个约束。

回答by David

Check that both fields are the same size and if the referenced field is unsigned then the referencing field should also be unsigned.

检查两个字段的大小是否相同，如果引用的字段是无符号的，则引用字段也应该是无符号的。

回答by Jim

I think you've got your tables a bit backwards. I'm assuming that Donors_Medical_Condtion links donors and medical conditions, so you want a foreign key for donors and conditions on that table.

我认为你的桌子有点倒退了。我假设 Donors_Medical_Condtion 将捐赠者和​​医疗条件联系起来，因此您需要该表上的捐赠者和条件的外键。

UPDATED

更新

Ok, you're also creating your tables in the wrong order. Here's the entire script:

好的，您也在以错误的顺序创建表格。这是整个脚本：

create table Donors (
donor_id int not null auto_increment primary key,
gender varchar(1) not null,
date_of_birth date not null,
first_name varchar(20) not null,
middle_name varchar(20),
last_name varchar(30) not null,
home_phone tinyint(10),
work_phone tinyint(10),
cell_mobile_phone tinyint(10),
medical_condition text,
other_details text );

create table Medical_Conditions (
condition_code int not null,
condition_name varchar(50) not null,
condition_description text,
other_details text,
primary key(condition_code) );

create table Donors_Medical_Condition (
donor_id int not null,
condition_code int not null,
seriousness text,
primary key(donor_id, condition_code),
foreign key(donor_id) references Donors(donor_id),
foreign key(condition_code) references Medical_Conditions(condition_code) );