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;