MySQL - Error Code 1215, cannot add foreign key constraint

Question

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"

i dont know what am i doing wrong.

Answer 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.

Answer 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,...)

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

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.

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

[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

Answer 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.

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

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

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;

Answer 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.

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

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

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-

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.

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.

Answer 6

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