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?

Ramble

Nowhere in any of the answer or question, was i able to find that this error can occur because of difference in character set. Thanks.. Cheers!!! – Gonta

Also, double check your column collation. Your table could be utf8mb4 but the column can have a different collation! – Lorusso

@Lorusso charset in my case but you get my upvote for getting me thinking. – Micrometeorology

Answer 1

Just throwing this into the mix of possible causes, I ran into this when the referencing table column had the same "type" but did not have the same signing.

In my case, the referenced table colum was TINYINT UNSIGNED and my referencing table column was TINYINT SIGNED. Aligning both columns solved the issue.

Salespeople

Solution suggested by @austen-hoogen has resolved my issue. But due to low reputation scores I am unable to vote and comment for his solution. In my case Primary key was integer, auto increment and unsigned. But foreign key (column of referencing table) was signed type. I changed it to unsigned and able to create a relation successfully. – Lynettalynette

Yes, went through the same issue. My primary key was BIGINT (20), and my referenced key in another table it was INT (10) unsigned. we nee to make sure they all match. ie. BIGINT (20), and in referenced table should also be BIGINT (20) – Crocked

Thank you! This solution resolved my problem too! I had int in the table where I was planing to set foreign key and bigint in the second table. – Taw

Answer 2

This error can also occur, if the references table and the current table don't have the same character set.

Mediative

After setting the character set as latin in referenced table then I started breath again after 3hrs. – Heyerdahl

This feels like madness that this could allow itself to be a problem, but I guess older tables can't all be upgraded to utf-8 at once or something. My new table was defaulting to latin1 – Unquestionable

I got this very same problem, and even though all tables were set to "Default Charset", I had to explicitly set utf8 to make it work. – Keitt

Answer 3

According to http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

So if the index in referenced table is exist and it is consists from several columns, and desired column is not first, the error shall be occurred.

The cause of our error was due to violation of following rule:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Ejection

This helped I had composit primary key where my foreign key was defined as second (i.e. CONSTRAINT pk_dataAssetId PRIMARY KEY (assetId, fk_key). Had to change it as first column in primary key constraint (i.e. CONSTRAINT pk_dataAssetId PRIMARY KEY (fk_key, assetId) – Cowskin

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)

Quadric

Thank you! Similar problem where I was using INT(11) with INT(11) but one was a primary key and thus unsigned while the other was signed! – Heavenly

Answer 5

Mine was a collation issue between the referenced table and the to be created table so I had to explicitly set the collation type of the key I was referencing.

• First I ran a query at referenced table to get its collation type

show table STATUS like '<table_name_here>';

• Then I copied the collation type and explicitly stated employee_id's collation type at the creation query. In my case it was utf8_general_ci

CREATE TABLE dbo.sample_db
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_id INT SIGNED NOT NULL,
  employee_id varchar(45) COLLATE utf8_general_ci NOT NULL,
  event_date_time DATETIME,
  CONSTRAINT sample_db_event_event_id_fk FOREIGN KEY (event_id) REFERENCES event (event_id),
  CONSTRAINT sample_db_employee_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
);

Brinton

Answer 6

For me it was just the charset and collation of the DB. I changed to utf8_unicode_ci and works

Zoosperm

Could you include an example of how to check the current charset/collation? – Cozenage

@their alter schema my_database default character set utf8 default collate utf8_general_ci; – Archle

Answer 7

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

https://mcmap.net/q/88641/-foreign-key-to-non-primary-key

Tinaret

Answer 8

In my case, it was an incompatibility with ENGINE and COLLATE, once i added ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci it worked

CREATE TABLE `some_table` (
      `id` varchar(36) NOT NULL,
      `col_id` varchar(36) NOT NULL,
      
      PRIMARY KEY (`id`),
      CONSTRAINT `FK_some_table_cols_col_id` FOREIGN KEY (`col_id`) REFERENCES `ref_table` (`id`) ON DELETE CASCADE,
      
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Gravel

Answer 9

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

Misdemean

Answer 10

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.

Nephron

Answer 11

In my case was created using integer for the id, and the referencing table was creating by default a foreign key using bigint.

This caused a big nightmare in my Rails app as the migration failed but the fields were actually created in DB, so they showed up in the DB but not in the schema of the Rails app.

Islet

Answer 12

And just to add to this , I've had the same issue today

Both fields were int of same length etc, however, one was unsigned and this was enough to break it.

Both needed to be declared as unsigned

Langston

Answer 13

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.

Coranto

Answer 14

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.

Syndactyl

Have you read an introduction to FKs or the manual? A FK references a UNIQUE; PK means UNIQUE NOT NULL. – Kt

Answer 15

Just to throw another solution in the mix. I had on delete set to set null but the field that i was putting the foreign key on was NOT nullable so making it nullable allowed the foreign key to be created.

As others have said the following things can be an issue

Field Length - INT -> BIGINT, VARCHAR(20) -> VARCHAR(40)
Unsigned - UNSIGNED -> Signed
Mixed Collations

Nygaard

Answer 16

I spent hours trying to get this to work. It turned out I had an older version of Heidi, 11.0.0.5919 which was not displaying the UNSIGNED attribute in the create table statement (Heidi bug), which I had used to copy from. Couldn't see it in the table design view either.

So the original table had an UNSIGNED attribute, but my foreign key didn't. The solution was upgrading Heidi, and adding the UNSIGNED attribute in the create table .

CREATE TABLE `extension` (
    `ExtensionId` INT NOT NULL,
    `AccountId` INT NOT NULL,
    PRIMARY KEY (`ExtensionId`) USING BTREE,
    INDEX `AccountId` (`AccountId`) USING BTREE,
    CONSTRAINT `AccountId` FOREIGN KEY (`AccountId`) REFERENCES  `accounts` (`AccountId`) ON UPDATE NO ACTION ON DELETE NO ACTION,
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

Change to:

`AccountId` INT UNSIGNED NOT NULL,

Mulciber

Answer 17

I had the same problem with writing this piece of code in the OnModelCreating method My problem was completely solved and my tables and migrations were created without errors. Please try it

var cascadeFKs = modelBuilder.Model.GetEntityTypes()
    .SelectMany(t => t.GetForeignKeys())
    .Where(fk => !fk.IsOwnership && fk.DeleteBehavior == DeleteBehavior.Cascade);

foreach (var fk in cascadeFKs)
    fk.DeleteBehavior = DeleteBehavior.Restrict;
      

Boding

Answer 18

It is mostly because the old table you are referring to does not have the suitable data type / collation / engine with the new table. The way to detect the difference is dumping that old table out and see how the database collect the information to have the dump script

mysqldump -uroot -p -h127.0.0.1 your_database your_old_table > dump_table.sql

It will give you enough information for you to compare

create table your_old_table
(
    `id`  varchar(32) not null,
) Engine = InnoDB DEFAULT CHARSET=utf8mb3;

This only works if you have the permission to dump your table scheme

Photoelectric

Answer 19

If Data type is same, Probably error is due to different Charset and Collation, try altering column as referenced column's Character set and Collate with a query something like this,

ALTER TABLE table_name MODIFY COLUMN
 column_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;

and then run the query for foreign key..

(alter charset of a table and database didn't work for me on incompatible error, until I alter specific column like my above suggestion)

Gehlenite

Answer 20

Faced a similiar issue,

My first table

CREATE TABLE customers ( 
                ID int PRIMARY KEY AUTO_INCREMENT,
                 firstname varchar(255) ,
                 middlename varchar(255) ,
                 lastname varchar(255) ,
                 email varchar(255) ,
                 country varchar(255) ,
                 state varchar(255) ,
                 address varchar(255) ,
                 zipcode varchar(255) ,
                 phone varchar(255) ,
                 dob varchar(255) ,
                 password varchar(255) ,
                 accountnumber varchar(255) ,
                 ddate TIMESTAMP
                                       ) ;

My second table

CREATE TABLE transactions ( 
            ID int PRIMARY KEY AUTO_INCREMENT,
             amount INT(255) ,
             type ENUM("Debit","Credit") ,
             from_to INT(255) ,
             description varchar(255) ,
             balance INT(255) ,
             accountnumber varchar(255) ,
             tdate TIMESTAMP,
             FOREIGN KEY(accountnumber) REFERENCES customers(accountnumber)
                                   ) ;

The second table could not be created. I solved it. By altering the first table with UNIQUE for the column name accountnumber

Alter table customers add UNIQUE(accountnumber);

Then after i tried creating the second table transactions, and it worked.

So the first table should have been something like this:

CREATE TABLE customers ( 
                ID int PRIMARY KEY AUTO_INCREMENT,
                 firstname varchar(255) ,
                 middlename varchar(255) ,
                 lastname varchar(255) ,
                 email varchar(255) ,
                 country varchar(255) ,
                 state varchar(255) ,
                 address varchar(255) ,
                 zipcode varchar(255) ,
                 phone varchar(255) ,
                 dob varchar(255) ,
                 password varchar(255) ,
                 accountnumber varchar(255) UNIQUE,
                 ddate TIMESTAMP
                                       ) ;

Calves

Answer 21

In my case I referred directly to a PRIMARY KEY and got the error shown above. After adding a "normal" index additionally to my primary key it worked:

ALTER TABLE `TableName` ADD INDEX `id` (`id`);

Now it looks like this:

When I try to drop the INDEX id again I get following error: (1553): Cannot drop index 'id': needed in a foreign key constraint.

EDIT: This is not a new question - I just want to show the way i solved this problem for me and what kind of problems may occur.

Symbolism