Mysql unique index error

MySQL UNIQUE KEY

In this article, we describe how to use unique keys/unique indexes in MySQL to ensure that the values ​​of one or several columns are unique.

In MySQL, we can define many constraints on a table, such as primary key constraints, foreign key constraints. A unique key is also a commonly used constraint to ensure that the values ​​in one or more columns in a table are unique.

We use unique key constraints in many systems, such as:

• There are login or email columns in the user table that are unique.
• The Product ID column in the Product table is unique.
• The order number column in the order table is unique.
• In the daily statistical report, the three columns of year, month and day are used as the combined unique key.

Compared with the primary key, the primary key is used to represent the identification of a row. The primary key generally adopts a value that has nothing to do with the business, such as auto-increment, UUID, etc. The unique key is generally used to constrain the uniqueness of business-related data.

Primary key columns cannot contain NULL values, while unique key columns can contain NULL values.

In MySQL, KEY is a synonym for INDEX . A unique key corresponds to a unique index.

UNIQUE syntax

To define a unique key, please use the UNIQUE keyword. You can define a unique key when creating a table or add a unique key by modifying the table after creating the table.

Define a column as a unique key

Here is the syntax to define a column as a unique column when creating a table:

Define multiple columns as a unique key

If the unique key contains multiple columns, use the following syntax:

• Multiple columns separated by commas are enclosed in parentheses after the keyword UNIQUE .
• constraint_name is the name used to define a constraint. It is optional. If you do not define a constraint name, MySQL will automatically generate one.

Add unique key syntax

We can also add a unique key to an existing table. Here is the syntax for adding a unique key:

Note that the MySQL server will return an error if there is already duplicate data in a column to be set as a unique key of an existing table.

delete unique key syntax

To remove a unique key from a table, you can use the ALTER TABLE statement or the DELETE INDEX statement:

• ALTER TABLE table_name DROP CONSTRAINT constraint_name
• ALTER TABLE table_name DROP INDEX index_name
• DROP INDEX index_name ON table_name

Unique key Examples

Let’s see some practical examples to understand the usage of the unique key constraint.

First, we first create a demo table user_hobby with a unique key with 2 columns:

Here, we define a constraint named unique_user_hobby unique, which contains 2 columns: user_id and hobby .

Then, we insert two rows for testing:

Now let’s look at the data in the table:

Unique constraint

Let’s insert another row with the same data as the existing user_id and hobby columns

MySQL returned an error: ERROR 1062 (23000): Duplicate entry ‘1-Football’ for key ‘user_hobby.unique_user_hobby’.

Here the unique key constraint unique_user_hobby avoids inserting duplicate data.

Delete unique key

Let’s delete the unique key with the following statement:

You need to provide the constraint name for deletion. If you don’t know its name or you didn’t specify a constraint name when creating the unique key, use the SHOW INDEX statement display all the index names in a table:

Note that the value in the key_name column is the constraint name.

Unique keys and NULL values

Unlike primary keys, unique keys allow columns within them to accept NULL values . However, the NULL value breaks the unique key constraint. That is, the unique key is invalid for the NULL value. Let’s look at the example below.

Now let’s modify the table creation statement just now, which allows hobby columns NULL :

Let’s insert two rows of the same data:

Then let’s look at the data in the table:

We saw duplicate data in two columns for the unique key. NULL makes the unique key invalid.

Conclusion

In this article, we learned the usage of unique keys/indexes in MySQL. Here are the main points of this article:

• Unique keys is used to ensure the uniqueness of values ​​in one or more columns in a table.
• You can use the UNIQUE keyword to define a unique key.
• Unique key columns can be NULL , but primary keys cannot NULL .

Источник

MySQL UNIQUE Constraint

Summary: in this tutorial, you will learn about MySQL UNIQUE constraint and how to use UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.

Introduction to MySQL UNIQUE constraint

Sometimes, you want to ensure values in a column or a group of columns are unique. For example, email addresses of users in the users table, or phone numbers of customers in the customers table should be unique. To enforce this rule, you use a UNIQUE constraint.

A UNIQUE constraint is an integrity constraint that ensures values in a column or group of columns to be unique. A UNIQUE constraint can be either a column constraint or a table constraint.

To define a UNIQUE constraint for a column when you create a table, you use this syntax:

In this syntax, you include the UNIQUE keyword in the definition of the column that you want to enforce the uniqueness rule. If you insert or update a value that causes duplicate in the column_name , MySQL rejects the change and issues an error.

This UNIQUE constraint is a column constraint. And you can use it to enforce the unique rule for one column.

To define a UNIQUE constraint for two or more columns, you use the following syntax:

In this syntax, you add a comma-separated list of columns in parentheses after the UNIQUE keyword. MySQL uses the combination of values in both column column_name1 and column_name2 to evaluate the uniqueness.

If you define a UNIQUE constraint without specifying a name, MySQL automatically generates a name for it. To define a UNIQUE constraint with a name, you use this syntax:

In this syntax, you specify the name of the UNIQUE constraint after the CONSTRAINT keyword.

MySQL UNIQUE constraint example

First, creates a new table named suppliers with the two UNIQUE constraints:

In this example, the first UNIQUE constraint is defined for the phone column:

And the second constraint is for both name and address columns:

Second, insert a row into the suppliers table.

Third, attempt to insert a different supplier but has the phone number that already exists in the suppliers table.

MySQL issued an error:

Fourth, change the phone number to a different one and execute the insert statement again.

Fifth, insert a row into the suppliers table with values that already exist in the columns name and address :

MySQL issued an error because the UNIQUE constraint uc_name_address was violated.

MySQL UNIQUE constraints and indexes

When you define a unique constraint, MySQL creates a corresponding UNIQUE index and uses this index to enforce the rule.

The SHOW CREATE TABLE statement shows the definition of the suppliers table:

As you can see from the output, MySQL created two UNIQUE indexes on the suppliers table: phone and uc_name_address .

The following SHOW INDEX statement displays all indexes associated with the suppliers table.

Drop a unique constraint

To drop a UNIQUE constraint, you use can use DROP INDEX or ALTER TABLE statement:

For example, the following statement drops the uc_name_address constraint on the suppliers table:

Execute the SHOW INDEX statement again to verify if the uc_name_unique constraint has been removed.

Add new unique constraint

The following ALTER TABLE ADD CONSTRAINT adds a unique constraint to a column of an existing table:

This statement adds a UNIQUE constraint uc_name_address back to the suppliers table:

Note that MySQL will not add a unique constraint if the existing data in the columns of specified in the unique constraint does not comply with the uniqueness rule.

In this tutorial, you have learned how to use the MySQL UNIQUE constraint to enforce the uniqueness of values in a column or group of columns of a table.

Источник

Как использовать уникальные индексы в MySQL и других базах данных

Если вы пользовались базами данных на протяжении некоторого времени, вы, вероятно, устанавливали первичный ключ в большинстве ваших таблиц.

Первичный ключ – это уникальный идентификатор для каждой записи, например:

В этом примере, столбец id – это наш первичный ключ. Когда мы производим операцию INSERT для телефонного номера, в случае, если мы не определили id , будет сгенерировано число AUTO_INCREMENT путем добавления единицы к наибольшему из существующих id .

Допустим, вы добавили следующие данные:

Затем выполним следующую операцию INSERT :

База данных не позволила бы добавить новую запись, потому что уже существует запись с id равным единице. Но к счастью, мы можем пренебречь id из нашей инструкции INSERT , сгенерировав id автоматически:

Теперь у нас есть четыре записи:

Мы можем добавить почти 17 миллионов записей прежде, чем значение id выйдет за пределы выделенной памяти.

Все отлично – за исключением того, что записи 1 и 4 идентичны. А что если мы хотим обеспечить уникальность всех телефонных номеров?

Уникальные индексы

Уникальные индексы работают почти таким же образом, как и первичный ключ. Однако вы можете использовать только один первичный ключ, тогда как уникальных индексов можно создать любое количество и с произвольным числом полей.

В нашем примере мы хотим гарантировать, что не существует двух записей с одной и той же страной, областью, основным и добавочным номером.

Мы можем сделать это, изменив нашу таблицу:

Обратите внимание, что имя индекса ix_phone не является обязательным. С другой стороны, мы бы могли создать нашу таблицу вновь:

Большинство баз данных поддерживает уникальные индексы, однако синтаксис SQL может отличаться.

Давайте попробуем вставить дублирующую запись, не указывая id :

Если вы используете MySQL, будет сгенерирована следующая ошибка:

При использовании практически любой базы данных вы можете гарантировать уникальность ваших записей телефонных номеров, независимо от того, как происходит вставка данных.

Значение NULL в MySQL

Я говорю « практически любой базы данных », потому что MySQL имеет странную особенность. Значение NULL рассматривается как уникальное значение – вот почему вы не можете использовать такие сравнения как value = NULL , а должны использовать value IS NULL .

К сожалению, это также влияет и на уникальные индексы, но ничего не было реализовано, чтобы исправить это.

Мы можем выполнить нашу первоначальную операцию INSERT несколько раз, и каждый раз будет создана новая запись, потому что поле extension по умолчанию имеет значение NULL и считается уникальным:

Да, это безумие. Я не в курсе о наличии этой проблемы в других базах данных, и даже MySQL работает должным образом, если вы используете подсистему хранения BDB . Данная особенность была отмечена как ошибка MySQL, но планы по ее исправлению не известны.

Решение : обеспечьте, чтобы все поля, определенные как уникальные индексы, не могли быть установлены в NULL . В данном примере мы могли бы указать, что дополнительного номера нет, установив значение 0 или 99999.

Или, возможно, мы могли бы сделать поле знаковым числом и установить значение -1 . Это ужасно, но это будет работать.

Несмотря на эту проблему, уникальные индексы во многих ситуациях полезны и помогут вам сохранить целостность данных тогда, когда другие программисты и пользователи не так ответственны!

Редакция Перевод статьи « How to Use Unique Indexes in MySQL and Other Databases »

Источник

Using MySQL UNIQUE Index To Prevent Duplicates

Summary: in this tutorial, you will learn how to use the MySQL UNIQUE index to prevent duplicate values in one or more columns in a table.

Introduction to the MySQL UNIQUE index

To enforce the uniqueness value of one or more columns, you often use the PRIMARY KEY constraint. However, each table can have only one primary key. Hence, if you want to have a more than one column or a set of columns with unique values, you cannot use the primary key constraint.

Luckily, MySQL provides another kind of index called UNIQUE index that allows you to enforce the uniqueness of values in one or more columns. Unlike the PRIMARY KEY index, you can have more than one UNIQUE index per table.

To create a UNIQUE index, you use the CREATE UNIQUE INDEX statement as follows:

Another way to enforce the uniqueness of value in one or more columns is to use the UNIQUE constraint.

When you create a UNIQUE constraint, MySQL creates a UNIQUE index behind the scenes.

The following statement illustrates how to create a unique constraint when you create a table.

In this statement, you can also use the UNIQUE INDEX instead of the UNIQUE KEY because they are synonyms.

If you want to add a unique constraint to an existing table, you can use the ALTER TABLE statement as follows:

MySQL UNIQUE Index & NULL

Unlike other database systems, MySQL considers NULL values as distinct values. Therefore, you can have multiple NULL values in the UNIQUE index.

This is how MySQL was designed. It is not a bug even though it was reported as a bug.

Another important point is that the UNIQUE constraint does not apply to NULL values except for the BDB storage engine.

MySQL UNIQUE index examples

Suppose, you want to manage contacts in an application. You also want that email of every contact in the contacts table must be unique.

To enforce this rule, you create a unique constraint in the CREATE TABLE statement as follows:

If you use the SHOW INDEXES statement, you will see that MySQL created a UNIQUE index for email column.

Let’s insert a row into the contacts table.

Now if you try to insert a row whose email is john.doe@mysqltutorial.org , you will get an error message.

Suppose you want the combination of first_name , last_name , and phone is also unique among contacts. In this case, you use the CREATE INDEX statement to create a UNIQUE index for those columns as follows:

Adding the following row into the contacts table causes an error because the combination of the first_name , last_name , and phone already exists.

In this tutorial, you have learned how to use the MySQL UNIQUE index to prevent duplicate values in the database.

Источник

In MySQL, we can define many constraints on a table, such as primary key constraints, foreign key constraints. A unique key is also a commonly used constraint to ensure that the values ​​in one or more columns in a table are unique.

We use unique key constraints in many systems, such as:

• There are login or email columns in the user table that are unique.
• The Product ID column in the Product table is unique.
• The order number column in the order table is unique.
• In the daily statistical report, the three columns of year, month and day are used as the combined unique key.

Compared with the primary key, the primary key is used to represent the identification of a row. The primary key generally adopts a value that has nothing to do with the business, such as auto-increment, UUID, etc. The unique key is generally used to constrain the uniqueness of business-related data.

Primary key columns cannot contain NULL values, while unique key columns can contain NULL values.

In MySQL, KEY is a synonym for INDEX. A unique key corresponds to a unique index.

UNIQUE syntax

To define a unique key, please use the UNIQUE keyword. You can define a unique key when creating a table or add a unique key by modifying the table after creating the table.

Define a column as a unique key

Here is the syntax to define a column as a unique column when creating a table:

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

Define multiple columns as a unique key

If the unique key contains multiple columns, use the following syntax:

CREATE TABLE table_name(
   column_name1 column_definition,
   column_name2 column_definition,
   ...,
   [CONSTRAINT constraint_name]
      UNIQUE(column_name1,column_name2)
);

Here:

• Multiple columns separated by commas are enclosed in parentheses after the keyword UNIQUE.
• constraint_name is the name used to define a constraint. It is optional. If you do not define a constraint name, MySQL will automatically generate one.

Add unique key syntax

We can also add a unique key to an existing table. Here is the syntax for adding a unique key:

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] UNIQUE (column_list);

Note that the MySQL server will return an error if there is already duplicate data in a column to be set as a unique key of an existing table.

delete unique key syntax

To remove a unique key from a table, you can use the ALTER TABLE statement or the DELETE INDEX statement:

• ALTER TABLE table_name DROP CONSTRAINT constraint_name
• ALTER TABLE table_name DROP INDEX index_name
• DROP INDEX index_name ON table_name

Unique key Examples

Let’s see some practical examples to understand the usage of the unique key constraint.

First, we first create a demo table user_hobby with a unique key with 2 columns:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `unique_user_hobby` UNIQUE(`user_id`, `hobby`)
);

Here, we define a constraint named unique_user_hobby unique, which contains 2 columns: user_id and hobby.

Then, we insert two rows for testing:

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, 'Football'), (1, 'Swimming');

Now let’s look at the data in the table:

SELECT * FROM user_hobby;

+----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |       1 | Football |
|        2 |       1 | Swimming |
+----------+---------+----------+
2 rows in set (0.00 sec)

Unique constraint

Let’s insert another row with the same data as the existing user_id and hobby columns

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, 'Football');

MySQL returned an error: ERROR 1062 (23000): Duplicate entry ‘1-Football’ for key ‘user_hobby.unique_user_hobby’.

Here the unique key constraint unique_user_hobby avoids inserting duplicate data.

Delete unique key

Let’s delete the unique key with the following statement:

DROP INDEX unique_user_hobby ON user_hobby;

You need to provide the constraint name for deletion. If you don’t know its name or you didn’t specify a constraint name when creating the unique key, use the SHOW INDEX statement display all the index names in a table:

SHOW INDEX FROM user_hobby;

+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_hobby |          0 | PRIMARY           |            1 | hobby_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_hobby |          0 | unique_user_hobby |            1 | user_id     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_hobby |          0 | unique_user_hobby |            2 | hobby       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

Note that the value in the key_name column is the constraint name.

Unique keys and NULL values

Unlike primary keys, unique keys allow columns within them to accept NULL values . However, the NULL value breaks the unique key constraint. That is, the unique key is invalid for the NULL value. Let’s look at the example below.

Now let’s modify the table creation statement just now, which allows hobby columns NULL:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45),
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `unique_user_hobby` UNIQUE(`user_id`, `hobby`)
);

Let’s insert two rows of the same data:

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, NULL), (1, NULL);

Then let’s look at the data in the table:

SELECT * FROM user_hobby;

+----------+---------+-------+
| hobby_id | user_id | hobby |
+----------+---------+-------+
|        1 |       1 | NULL  |
|        2 |       1 | NULL  |
+----------+---------+-------+
2 rows in set (0.00 sec)

We saw duplicate data in two columns for the unique key. NULL makes the unique key invalid.

Conclusion

In this article, we learned the usage of unique keys/indexes in MySQL. Here are the main points of this article:

• Unique keys is used to ensure the uniqueness of values ​​in one or more columns in a table.
• You can use the UNIQUE keyword to define a unique key.
• Unique key columns can be NULL, but primary keys cannot NULL.

Finding out why Foreign key creation fail

When MySQL is unable to create a Foreign Key, it throws out this generic error message:

ERROR 1215 (HY000): Cannot add foreign key constraint

– The most useful error message ever.

Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.

mysql> SHOW ENGINE INNODB STATUS;

That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-29 13:40:56 0x7f3cb452e700 Error in foreign key constraint of table test_database/my_table:
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 idx_name FOREIGN KEY (employee_id) REFERENCES employees (id)
The index in the foreign key in table is idx_name
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This output could give you some clue about the actual reason why MySQL could not create your Foreign Key

Reason #1 – Missing unique index on the referenced table

This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:

In the all below examples, we’ll use a simple ‘Employee to Department” relationship:

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     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 have not created the table with PRIMARY KEY or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id column and departments.id column:

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

Let’s look at the detailed error:

mysql> SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-31 09:25:13 0x7fddc805f700 Error in foreign key constraint of table foreign_key_1/#sql-5ed_49b:
FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id):
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This is because we don’t have any unique index on the referenced table i.e. departments. We have two ways of fixing this:

Option 1: Primary Keys

Let’s fix this by adding a primary key departments.id

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

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.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Option 2: Unique Index

mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

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.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Reason #2 – Different data types on the columns

MySQL requires the columns involved in the foreign key to be of the same data types.

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows 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 that employees.department_id is int while departments.id is char(20). Let’s try to create a foreign key now:

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

Let’s fix the type of departments.id and 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

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.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #3 – Different collation/charset type on the table

This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):

Let’s start from scratch to explain this scenario:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=latin1;
Query OK, 0 rows affected (0.08 sec)

You may notice that we are using a different character set (utf8 and latin1` for both these 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): Cannot add foreign key constraint

It failed because of different character sets. Let’s fix that.

mysql> SET foreign_key_checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 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 a different collation/character set, use this script to generate a list of commands to fix all tables at once:

mysql --database=your_database -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}'

Reason #4 – Different collation types on the columns

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

Let’s try to reproduce this from scratch:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id char(26) CHARACTER SET utf8,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE departments(
    ->     id char(26) CHARACTER SET latin1,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

We are using a different character set for employees.department_id and departments.id (utf8 and latin1). 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): Cannot add foreign key constraint

Nope, as expected. Let’s fix that by changing the character set of departments.id to match with employees.department_id:

mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

It works now!

Reason #5 -Inconsistent data

This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.

Let’s setup some inconsistent data to reproduce this problem:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows 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 a department_id in employees table that will not exist in departments.id:

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

Let’s create a foreign key now 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): Cannot add or update a 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 atleast more useful. We can fix this in two ways. Either by adding the missing department in departments table or by deleting all the employees with the missing department. We’ll do the first option now:

mysql> INSERT INTO departments VALUES (145, 'HR');
Query OK, 1 row 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 have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.

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

SQLSTATE[HY000]: General error: 3780 Referencing column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are incompatible.