MySQL cannot create foreign key constraint

Question

I'm having some problems creating a foreign key to an existing table in a mysql database.

I have the table 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    |       |
+-------------+------------------+------+-----+---------+-------+

and I wan't to create a new table called sample_df referencing this, using the following:

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

But I get the error:

ERROR 1215 (HY000): Cannot add foreign key constraint

To get some more information I did:

SHOW ENGINE INNODB STATUS\G

From which I got:

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.

To me the column types seem to match, since they are both varchar(45).(I also tried setting the experiment column to not null, but this didn't fix it) So I guess the problem must be that Cannot find an index in the referenced table where the referenced columns appear as the first columns. But I'm not quite sure what this means, or how to check/fix it. Does anyone have any suggestions? And what is meant by first columns?

Answer 1

I had this error as well. None of the answers pertained to me. In my case, my GUI automatically creates a table with a primary unique identifier as "unassigned". This fails when I try and create a foreign key and gives me the exact same error. My primary key needs to be assigned.

If you write the SQL itself like so id int unique auto_increment then you don't have this issue but for some reason my GUI does this instead id int unassigned unique auto_increment.

Hope this helps someone else down the road.

Answer 2

In my case, it turned out the referenced column wasn't declared primary or unique.

https://stackoverflow.com/a/18435114/1763217

Answer 3

Referencing the same column more than once in the same constraint also produces this Cannot find an index in the referenced table error, but can be difficult to spot on large tables. Split up the constraints and it will work as expected.

Answer 4

As mentioned @Anton, this could be because of the different data type. In my case I had primary key BIGINT(20) and tried to set foreight key with INT(10)

Answer 5

The exact order of the primary key also needs to match with no extra columns in between.

I had a primary key setup where the column order actually matches, but the problem was the primary key had an extra column in it that is not part of the foreign key of the referencing table

e.g.) table 2, column (a, b, c) -> table 1, column (a, b, d, c) -- THIS FAILS

I had to reorder the primary key columns so that not only they're ordered the same way, but have no extra columns in the middle:

e.g.) table 2, column (a, b, c) -> table 1, column (a, b, c, d) -- THIS SUCCEEDS

Answer 6

In some cases, I had to make the referenced field unique on top of defining it as the primary key.

But I found that not defining it as unique doesn't create a problem in every case. I have not been able to figure out the scenarios though. Probably something to do with nullable definition.