5 Reasons You Can't Create MySQL Foreign Key Constraints - Jiga (2023)

Find out why foreign key creation fails

yes mysql nounknown key, throws this generic error message:

ERROR 1215 (HY000): Unable to add foreign key constraint

- The most useful error message ever.

Fortunately, MySQL has this handy command that can give you the real reason why the foreign key could not be created.

mysql> MOSTRAR STATUS INNODB DO MOTOR;

This will print a lot of results, but the part we're interested in is under the header"LAST FOREIGN KEY FAILURE":

------------------------ CURRENT FOREIGN KEY ERROR -------- --2020 -08-29 13:40:56 0x7f3cb452e700 Foreign key constraint on table test_database/my_table failed: There are no indexes on the referenced table that have columns as first columns, or the data types in the referenced table do not match those of the table. Restriction:,RESTRICTIONidx_nameUNKNOWN KEY (Employee ID) REFERENCESEmployees(I WENT)The index is on the foreign key in the tableidx_nameSee http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for the correct foreign key definition.

This output might give you a clue as to the real reason why MySQL couldn't create your foreign key.

Reason #1: A unique index is missing on the referenced table

This is probably the most common reason why MySQL doesn't create its foreign key constraint. Let's look at an example with a new database and new tables:

In all of the following examples, we use a simple "employee to department" relationship:

mysql> CREATE DATABASE Foreign_key_1; Query OK, 1 queue affected (0.00 sec)

mysql> USE Foreign_Key_1;Database changed mysql> CREATE TABLE Employee( -> ID int, -> Name varchar(20), -> Department_ID int -> ); Query OK, 0 rows affected (0.08 sec) mysql> CREATE TABLE departments (-> id int, -> name varchar(20) ->); Query OK, 0 rows affected (0.07 sec)

As you may have noticed, we didn't create the tablePRIMARY KEYor unique indexes. Now let's try to create a foreign key constraint in the middleEmployee ID.departmentdivide youAbteilungen.idTo divide:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); ERROR 1215 (HY000): Unable to add foreign key constraint

Let's see the detailed error:

mysql> MOSTRAR STATUS INNODB DO MOTOR;

------------------------ CURRENT FOREIGN KEY ERROR -------- --2020 -08-31 09:25:13 0x7fddc805f700 Foreign key constraint on table Foreign_key_1/#sql-5ed_49b failed: FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id):Cannot find an index on the referenced table in which the referenced columns appear as the first columns, or the column types in the table and the referenced table do not match the constraint.Note that the internal storage type of ENUM and SET has changed in tables created with >= InnoDB-4.1.12 and such columns in old tables cannot be referenced by columns in new tables. See http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for the correct foreign key definition.

This is because we don't have a unique index on the referenced table i.e.departments. We have two options to fix this:

Option 1: primary key

Let's fix that by adding aprimary key Abteilungen.id

mysql> ALTER TABLE departments ADD PRIMARY KEY (id); Query OK, 0 lines affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 lines affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0

Option 2: single index

mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id); Query OK, 0 lines affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE Employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); Query OK, 0 lines affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0

Reason #2 – Different data types in columns

MySQL requires that the columns involved in the foreign key have the same data type.

mysql> CREATE DATABASE Foreign_Key_1; Query OK, 1 line affected (0.00 sec) mysql> USE Foreign_Key_1; Changed database > PRIMARY KEY (id) -> ); Query OK, 0 lines affected (0.06 sec) mysql> CREATE TABLE departments (-> id char(20), -> name varchar(20), -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.07 sec)

You may have noticed thatEmployee ID.departmentEsE TwhileAbteilungen.idEscharacters(20). Now let's try to create a foreign key:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); ERROR 1215 (HY000): Unable to add foreign key constraint

Let's define the type ofAbteilungen.idand try to create the foreign key again:

mysql> ALTER TABLE departments MODIFY id INT; Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0

Now it works!

Reason #3: Different type of collation/charset in table

This is a surprising and difficult reason to understand. Let's create two tables with a different classification (or also called character set):

Let's start from scratch to explain this scenario:

mysql> CREATE DATABASE external_key_1; Query OK, 1 line affected (0.00 sec) mysql> USE Foreign_Key_1; Changed database mysql> CREATE TABLE employee(-> id int, -> name varchar(20), -> department_id int, -> PRIMARY KEY(id) ->) ENGINE=InnoDB CHARACTER SET=utf8; Query OK, 0 lines affected (0.06 sec) mysql> CREATE TABLE departments (-> id int, -> name varchar(20), -> PRIMARY KEY (id) ->) ENGINE = CHARACTER SET InnoDB = latin1; Query OK, 0 rows affected (0.08 sec)

You may notice that we used a different character set (utf8Ylatin1` for these two tables. Let's try to create the foreign key:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); ERROR 1215 (HY000): Unable to add foreign key constraint

It failed due to different character sets. Let's fix this.

mysql> SET Foreign_Key_Checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET external_key_check = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE Employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0

If you have many tables with different collation/charset, use this script to generate a list of commands to fix all tables at once:

mysql --database=su_base de dados -B -N -e "TABELLEN ANZEIGEN" | awk '{print "SET Foreign_Key_Checks = 0; ALTER TABLE", $1, "CONVERTIR EN CONJUNTO DE CARACTERES utf8 COLLATE utf8_general_ci; SET Foreign_Key_Checks = 1; "}'

Reason #4: Different types of column sorting

This is a rare motif, similar to motif #3 above but at column level.

Let's try to reproduce this from scratch:

mysql> CREATE DATABASE external_key_1; Query OK, 1 line affected (0.00 sec) mysql> USE Foreign_Key_1; Changed database mysql> CREATE TABLE employee(-> id int, -> name varchar(20), -> department_id char(26) CHAR SET utf8, -> PRIMARY KEY(id) ->); Query OK, 0 rows affected (0.07 sec)mysql> CREATE TABLE departments( -> id char(26) CHAR SET latin1, -> name varchar(20), -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.08 sec)

We use a different character set forEmployee ID.departmentYAbteilungen.id(utf8YLatin1). Let's check if the foreign key can be created:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); ERROR 1215 (HY000): Unable to add foreign key constraint

No, as expected. Let's fix this by changing the character set fromAbteilungen.idPhosphorEmployee ID.department:

mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8; query OK, 0 rows affected (0.20 sec) records: 0 duplicates: 0 warnings: 0mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments( id); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0

Now it works!

Reason #5 - Inconsistent Data

That would be the most obvious reason. There is a foreign key to ensure that your data remains consistent between parent and child tables. So when you create the foreign key, the expectation is that the existing data is already consistent.

Let's set up some inconsistent data to reproduce this issue:

mysql> CREATE DATABASE external_key_1; Query OK, 1 line affected (0.00 sec) mysql> USE Foreign_Key_1; Changed database mysql> CREATE TABLE employee(-> id int, -> name varchar(20), -> department_id int, -> PRIMARY KEY(id) ->); Query OK, 0 lines affected (0.06 sec) mysql> CREATE TABLE Departments( -> id int, -> name varchar(20), -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.08 sec)

Let's insert adepartment idEmEmployeesTable that will not exist inAbteilungen.id:

mysql> INSERT INTO employee VALUES(1, 'Amber', 145); Query OK, 1 line affected (0.01 sec)

Now let's create a foreign key and see if it works:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); ERROR 1452 (23000): Unable to add or update child row: A foreign key constraint fails (`foreign_key_1`.`#sql-5ed_49b ` , CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` ( `id`) )

This error message is at least more helpful. We can fix this in two ways. If adding the missing departmentdepartmentstable or excluding all employees from the missing department. Now we do the first option:

mysql> INSERT INTO department VALUES(145, 'HR'); Query OK, 1 line affected (0.00 sec)

Let's try to create the foreign key again:

mysql> ALTER TABLE Employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments (id); Query OK, 1 row affected (0.24 sec) Records: 1 Duplicates: 0 Warnings: 0

It worked this time.

So, we've seen 5 different ways that creating foreign keys can fail, and possible solutions on how to fix them. If you've found a reason that isn't on the list above, please add it in the comments.

If you are using MySQL 8.x, the error message will be slightly different:

SQLSTATE[HY000]: general error: 3780 Referenced column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are not compatible.