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

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.

我不知道我做错了什么。

6 个解决方案

#1

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上创建索引或更改主键st,则条件码首先应该能够创建索引。

#2

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

要定义外键,引用的父字段必须在其上定义索引。

As per documentation on foreign key constraints:

根据外键约束的文档:

REFERENCES tbl_name (index_col_name,...)

参考tbl_name(index_col_name,...)

Define an INDEX on condition_code in parent table Donors_Medical_Condition and it should be working.

在父表Donors_Medical_Condition中的condition_code上定义一个INDEX,它应该正常工作。

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 key in Donors_Medical_Condition table but not in Donors_Medical_Conditions table. The latter seems to be a parent.

但似乎你错误地定义了表的顺序和引用。您应该在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]

[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

reference_option:RESTRICT | CASCADE | SET NULL |没有行动

#3

A workaround for 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;

#4

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

#5

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;

SHOW ENGINE 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工作台删除了所有关系,但仍然存在约束。原因是因为这个约束已经在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.

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

#6

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

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