I just wanted to add this case as well for VARCHAR foreign key relation. I spent the last week trying to figure this out in MySQL Workbench 8.0 and was finally able to fix the error.
Short Answer:
The character set and collation of the schema, the table, the column, the referencing table, the referencing column and any other tables that reference to the parent table have to match.
Long Answer:
I had an ENUM datatype in my table. I changed this to VARCHAR and I can get the values from a reference table so that I don’t have to alter the parent table to add additional options. This foreign-key relationship seemed straightforward but I got 1215 error. arvind’s answer and the following link suggested the use of
SHOW ENGINE INNODB STATUS;
On using this command I got the following verbose description for the error with no additional helpful information
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/8.0/en/innodb-foreign-key-constraints.html for correct foreign key definition.
After which I used SET FOREIGN_KEY_CHECKS=0; as suggested by Arvind Bharadwaj and the link here:
This gave the following error message:
Error Code: 1822. Failed to add the foreign key constraint. Missing
index for constraint
At this point, I ‘reverse engineer’-ed the schema and I was able to make the foreign-key relationship in the EER diagram. On ‘forward engineer’-ing, I got the following error:
Error 1452: Cannot add or update a child row: a foreign key constraint
fails
When I ‘forward engineer’-ed the EER diagram to a new schema, the SQL script ran without issues. On comparing the generated SQL from the attempts to forward engineer, I found that the difference was the character set and collation. The parent table, child table and the two columns had utf8mb4 character set and utf8mb4_0900_ai_ci collation, however, another column in the parent table was referenced using CHARACTER SET = utf8 , COLLATE = utf8_bin ; to a different child table.
For the entire schema, I changed the character set and collation for all the tables and all the columns to the following:
CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
This finally solved my problem with 1215 error.
Side Note:
The collation utf8mb4_general_ci works in MySQL Workbench 5.0 or later. Collation utf8mb4_0900_ai_ci works just for MySQL Workbench 8.0 or higher. I believe one of the reasons I had issues with character set and collation is due to MySQL Workbench upgrade to 8.0 in between. Here is a link that talks more about this collation.
I just wanted to add this case as well for VARCHAR foreign key relation. I spent the last week trying to figure this out in MySQL Workbench 8.0 and was finally able to fix the error.
Short Answer:
The character set and collation of the schema, the table, the column, the referencing table, the referencing column and any other tables that reference to the parent table have to match.
Long Answer:
I had an ENUM datatype in my table. I changed this to VARCHAR and I can get the values from a reference table so that I don’t have to alter the parent table to add additional options. This foreign-key relationship seemed straightforward but I got 1215 error. arvind’s answer and the following link suggested the use of
SHOW ENGINE INNODB STATUS;
On using this command I got the following verbose description for the error with no additional helpful information
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/8.0/en/innodb-foreign-key-constraints.html for correct foreign key definition.
After which I used SET FOREIGN_KEY_CHECKS=0; as suggested by Arvind Bharadwaj and the link here:
This gave the following error message:
Error Code: 1822. Failed to add the foreign key constraint. Missing
index for constraint
At this point, I ‘reverse engineer’-ed the schema and I was able to make the foreign-key relationship in the EER diagram. On ‘forward engineer’-ing, I got the following error:
Error 1452: Cannot add or update a child row: a foreign key constraint
fails
When I ‘forward engineer’-ed the EER diagram to a new schema, the SQL script ran without issues. On comparing the generated SQL from the attempts to forward engineer, I found that the difference was the character set and collation. The parent table, child table and the two columns had utf8mb4 character set and utf8mb4_0900_ai_ci collation, however, another column in the parent table was referenced using CHARACTER SET = utf8 , COLLATE = utf8_bin ; to a different child table.
For the entire schema, I changed the character set and collation for all the tables and all the columns to the following:
CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
This finally solved my problem with 1215 error.
Side Note:
The collation utf8mb4_general_ci works in MySQL Workbench 5.0 or later. Collation utf8mb4_0900_ai_ci works just for MySQL Workbench 8.0 or higher. I believe one of the reasons I had issues with character set and collation is due to MySQL Workbench upgrade to 8.0 in between. Here is a link that talks more about this collation.
When you try to create a foreign key constraint between two tables, you may encounter the MySQL error 1215 that says Cannot add foreign key constraint.
For example, suppose we have a table called cities with the following data:
# cities table
+----+-----------+
| id | name |
+----+-----------+
| 1 | London |
| 2 | York |
| 3 | Bristol |
| 4 | Liverpool |
+----+-----------+
Then, suppose we want to create a table named users with a foreign key constraint, referencing the id column from the cities table.
Here’s how we might do it:
CREATE TABLE `users` (
`user_id` int unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`city_id` int DEFAULT NULL,
PRIMARY KEY (`user_id`),
FOREIGN KEY (`city_id`) REFERENCES cities(id)
);
The response from MySQL may look like this:
ERROR 1215 (HY000): Cannot add foreign key constraint
Unfortunately, there are many issues that could cause this error.
This tutorial will list the most common cause for ERROR 1215 and give you suggestions on how to fix them.
Make sure that you are using the correct syntax
The first thing to do is to make sure that you are using the correct syntax for creating the FOREIGN KEY constraint.
The syntax to add a foreign key on CREATE TABLE statement must follow this pattern:
FOREIGN KEY (`[target_column_name]`)
REFERENCES [origin_table_name]([origin_column_name])
You must replace [target_column_name] next to the FOREIGN KEY syntax with the column name in the current table, while [origin_table_name] and [origin_column_name] must refer to the table and column name of an existing table.
Once you have the correct syntax, make sure that there’s no typo in [target_column_name], [origin_table_name], and [origin_column_name] or you may trigger the same error.
Once you are sure you have the correct syntax, let’s check the engine used by your tables next.
Make sure your tables are using InnoDB engine
You need to check whether the existing table and the table you want to create are using InnoDB engine.
This is because the MyISAM engine doesn’t support adding foreign key constraints, so when you try to add a foreign key constraint to the table, it will trigger the ERROR 1215.
To check the engine of your existing table, you need to run the SHOW TABLE STATUS statement like this:
SHOW TABLE STATUS WHERE name = 'cities';
If you’re using the mysql command line client, then add a G next to the table name to organize the output as lists instead of a table.
Here’s an example output from the command line client:
mysql> SHOW TABLE STATUS WHERE name = 'cities'G
*************************** 1. row ***************************
Name: cities
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 20
Data_length: 80
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: 5
Create_time: 2021-11-13 11:32:14
Update_time: 2021-11-13 11:32:14
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
As you can see from the highlighted line, the cities table is using the MyISAM engine.
You can change the engine of your MySQL table by using the ALTER TABLE statement as follows:
ALTER TABLE cities ENGINE = InnoDB;
Once you altered the table engine, you can try to add the foreign key constraint to the new table again.
The default engine used for CREATE TABLE statement should be InnoDB, but you can add the engine explicitly as shown below:
CREATE TABLE `users` (
`user_id` int unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`city_id` int DEFAULT NULL,
PRIMARY KEY (`user_id`),
FOREIGN KEY (`city_id`) REFERENCES cities(id)
)
ENGINE = InnoDB;
If the error still happens, then it’s time to check the data type of the two columns.
Make sure the two columns are using the same data type
When adding foreign key constraints, the referenced column and the referencing column must both have the same data type.
An important tip here is to look at the full specification of your column using the DESCRIBE statement.
For example,
DESCRIBE cities;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | tinytext | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
As you can see from the output above, the field id has the data type of int unsigned, but the referencing column city_id on the CREATE TABLE statement has the int type:
CREATE TABLE `users` (
`user_id` int unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`city_id` int DEFAULT NULL,
PRIMARY KEY (`user_id`),
FOREIGN KEY (`city_id`) REFERENCES cities(id)
)
Keep in mind that the two columns type for the foreign key constraint must exactly match (int signed with int signed, or int unsigned with int unsigned).
You need to fix this issue by either altering the referenced column or the referencing column until they have the same type
Now that you have the same type for the two columns, you can try adding the foreign key again.
Adding ON DELETE / UPDATE SET NULL clause on a NOT NULL column
One more thing that could cause this error is when you add the ON DELETE SET NULL clause to the FOREIGN KEY constraint while the actual column is set to NOT NULL
Take a look at the highlighted lines on the example below:
CREATE TABLE `users` (
`user_id` int unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`city_id` int unsigned NOT NULL,
PRIMARY KEY (`user_id`),
FOREIGN KEY (`city_id`) REFERENCES cities(id)
ON DELETE SET NULL
)
While the city_id column is specified as NOT NULL, the ON DELETE SET NULL clause on the FOREIGN KEY constraint will cause the same error.
You need to either set the column as DEFAULT NULL:
`city_id` int unsigned DEFAULT NULL
Or you need to remove the ON DELETE SET NULL clause.
The same thing also happens when you add the ON UPDATE SET NULL clause to the FOREIGN KEY constraint.
For VARCHAR columns, make sure you have the same collation for both tables
When you’re adding a foreign key constraint with columns of VARCHAR types, you need to make sure that both tables are using the same collation.
Just like the engine type, you can check the table collation using the SHOW TABLE STATUS statement.
Here’s an example output from my database:
mysql> SHOW TABLE STATUS WHERE name = 'cities'G
*************************** 1. row ***************************
Name: cities
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 20
Data_length: 80
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: 5
Create_time: 2021-11-13 11:32:14
Update_time: 2021-11-13 11:32:14
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
Then, you can check the Collation and Charset you need to use in your CREATE TABLE statement by running the SHOW COLLATION statement as follows:
SHOW COLLATION LIKE '[collation_name]';
The result for collation utf8mb4_0900_ai_ci is as follows:
mysql> SHOW COLLATION LIKE 'utf8mb4_0900_ai_ci%'G
*************************** 1. row ***************************
Collation: utf8mb4_0900_ai_ci
Charset: utf8mb4
Id: 255
Default: Yes
Compiled: Yes
Sortlen: 0
Pad_attribute: NO PAD
1 row in set (0.01 sec)
In your CREATE TABLE statement, add the COLLATE and CHARSET options as shown below:
CREATE TABLE table_name(
-- ...
)
ENGINE = InnoDB
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
That should allow you to add foreign key constraints with columns of VARCHAR type.
Conclusion
Through this tutorial, you’ve learned five things that you can check to resolve the MySQL error 1215 Cannot add foreign key constraint.
This error message is not helpful when trying to find the cause, and in recent MySQL versions, the error has been replaced with more descriptive ones.
For example, when you type the wrong table name, you’ll have ERROR 1824 saying Failed to open the referenced table as shown below:
mysql> CREATE TABLE `users` (
-> `user_id` int unsigned NOT NULL AUTO_INCREMENT,
-> `first_name` varchar(45) NOT NULL,
-> `last_name` varchar(45) NOT NULL,
-> `city_id` int unsigned DEFAULT NULL,
-> PRIMARY KEY (`user_id`),
-> FOREIGN KEY (`city_id`) REFERENCES citiess(id)
-> );
ERROR 1824 (HY000): Failed to open the referenced table 'citiess'
The error message above directly points you to the problem with the syntax.
In another example, different column data types will make MySQL throw ERROR 3780 saying the columns are incompatible:
mysql> CREATE TABLE `users` (
-> `user_id` int unsigned NOT NULL AUTO_INCREMENT,
-> `first_name` varchar(45) NOT NULL,
-> `last_name` varchar(45) NOT NULL,
-> `city_id` int DEFAULT NULL,
-> PRIMARY KEY (`user_id`),
-> FOREIGN KEY (`city_id`) REFERENCES cities(id)
-> );
ERROR 3780 (HY000): Referencing column 'city_id' and referenced column 'id'
in foreign key constraint 'users_ibfk_1' are incompatible.
Unfortunately, I wasn’t able to pinpoint the exact MySQL version that updates the error messages.
I have updated MySQL to the latest version 8.0.27, so if you have some free time, you might want to upgrade your MySQL version to at least version 8 so that it gives more helpful error messages.
Good luck in resolving the error! 👍
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: , CONSTRAINTidx_nameFOREIGN KEY (employee_id) REFERENCESemployees(id) The index in the foreign key in table isidx_namePlease 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 constraintLet’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: 0Option 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: 0Reason #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 constraintLet’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: 0It 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 constraintIt 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: 0If 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 constraintNope, 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: 0It 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: 0It 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. Introduction
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:
create table parent (
id int not null primary key,
name char(80)
) engine=innodb;
create table child (
id int not null,
name char(80),
parent_id int,
foreign key(parent_id) references parent(id)
) engine=innodb;
As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:
- InnoDB (both innodb_plugin and XtraDB)
- PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/)
- SolidDB for MySQL (http://sourceforge.net/projects/soliddb/)
- ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ and http://scaledb.com/pdfs/TechnicalOverview.pdf)
- MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)
MariaDB foreign key syntax is documented at https://mariadb.com/kb/en/mariadb/foreign-keys/ (and MySQL at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).
However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.
Constraint name not unique
Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:
-------------- CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.45 sec) -------------- CREATE TABLE t2 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") -------------- show warnings -------------- +---------+------+--------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") | | Warning | 1022 | Can't write; duplicate key in table 't2' | +---------+------+--------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:
show engine innodb status -------------- ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 12:37:48 7f44a1111700 Error in foreign key constraint creation for table `test`.`t2`. A foreign key constraint of name `test`.`test` already exists. (Note that internally InnoDB adds 'databasename' in front of the user-defined constraint name.) Note that InnoDB's FOREIGN KEY system tables store constraint names as case-insensitive, with the MySQL standard latin1_swedish_ci collation. If you create tables or databases whose names differ only in the character case, then collisions in constraint names can occur. Workaround: name your constraints explicitly with unique names.
In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.14 sec) -------------- CREATE TABLE t2 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 121) -------------- show warnings -------------- +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 121 | Create or Alter table `test`.`t2` with foreign key constraint failed. Foreign key constraint `test/test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`). | | Error | 1005 | Can't create table 'test.t2' (errno: 121) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
No index
There should be an index for columns in a referenced table that contains referenced columns as the first columns.
create table t1(a int, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.46 sec) -------------- create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table 'test/t2' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. | | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:
LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:31 7f30e1520700 Error in foreign key constraint of table test/t2: foreign key a (a) references t1(a)) engine=innodb: 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. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
create table t1(a int, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.16 sec) -------------- create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key a (a) references t1(a)) engine=innodb. | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Referenced table not found
A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:
create table t1 (f1 integer primary key) engine=innodb -------------- Query OK, 0 rows affected (0.47 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t11(f1) -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------+ show engine innodb status -------------- LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:34 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2: foreign key (f1) references t11(f1): Cannot resolve table name close to: (f1)
Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
create table t1 (f1 integer primary key) engine=innodb -------------- Query OK, 0 rows affected (0.11 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t11(f1) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1). | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- show engine innodb status -------------- 150730 13:50:36 Error in foreign key constraint of table `test`.`t1`: Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1).
Temporary tables
Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+--------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- show engine innodb status -------------- LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_1: foreign key(a) references t1(a)) engine=innodb: Cannot resolve table name close to: (a)) engine=innodb -------------- alter table t1 add foreign key(b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:
create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.04 sec) -------------- create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table `tmp`.`t2`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb. | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- alter table t1 add foreign key(b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `tmp`.`t1`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(b) references t1(a). | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Column count does not match
There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:
create table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.17 sec) -------------- alter table t1 add foreign key(a,b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-4856_1' (errno: 150) -------------- show warnings -------------- +-------+------+----------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------+ | Error | 1005 | Can't create table 'test.#sql-4856_1' (errno: 150) | +-------+------+----------------------------------------------------+ 1 row in set (0.00 sec) -----------------+ show engine innodb status; -----------------+ LATEST FOREIGN KEY ERROR ------------------------ 150730 15:15:57 Error in foreign key constraint of table test/#sql-4856_1: foreign key(a,b) references t1(a): Syntax error close to: 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_2: foreign key(b) references t1(a): Cannot resolve table name close to: (a)
The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:
create table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.14 sec) -------------- alter table t1 add foreign key(a,b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Foreign key constraint parse error in foreign key(a,b) references t1(a) close to ). Too few referenced columns, you have 1 when you should have 2. | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Incorrect cascading
A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:
create table t1 (f1 integer not null primary key) engine=innodb -------------- Query OK, 0 rows affected (0.40 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+--------------------------------------------------------------------------------------------- --------+ show engine innodb status; --------+ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:37 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2: foreign key (f1) references t1(f1) on update set null: You have defined a SET NULL condition though some of the columns are defined as NOT NULL.
Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:
create table t1 (f1 integer not null primary key) engine=innodb -------------- Query OK, 0 rows affected (0.10 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in foreign key (f1) references t1(f1) on update set null close to on update set null. | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Incorrect types
Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb -------------- Query OK, 0 rows affected (0.47 sec) -------------- create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table 'test/t2' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. | | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) --------+ show engine innodb status; --------+ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:39 7f30e1520700 Error in foreign key constraint of table test/t2: foreign key(a) references t1(f1)) engine=innodb: 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. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb -------------- Query OK, 0 rows affected (0.15 sec) -------------- create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Conclusions
There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!
References
- https://mariadb.atlassian.net/browse/MDEV-6697
- https://mariadb.atlassian.net/browse/MDEV-8524
Дата: 2.12.2016
Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru
Функционирование внешних ключей в MySQL имеет много нюансов и ограничений из-за чего существует немало возможностей получить ошибку при работе с ними. Одна из проблем состоит в том, что сообщения об ошибках содержат мало полезной информации и не указывают причину возникновения ошибки. В данной статье дается объяснение как получить дополнительную информацию об ошибке и приведен полный список причин возникновения ошибок внешних ключей. Каждая причина снабжена уникальным буквенно-цифровым кодом (А4, Б1, ..), использующимся в сводной таблице в конце статьи, которая поможет вам быстро диагностировать проблему.
Внешний ключ — это поле (или набор полей) в таблице, называемой дочерней, которое ссылается на поле (или набор полей) в таблице, называемой родительской. Дочерняя и родительская таблицы могут совпадать, т.е. таблица будет ссылаться на саму себя. Внешние ключи позволяют связать записи в двух таблицах по определенным полям так, что при обновлении поля в родительской автоматически происходит изменение записи в дочерней таблице.
В MySQL внешние ключи не реализованы на уровне сервера, их поддержка зависит от используемого хранилища данных. Содержание статьи справедливо для InnoDB (в том числе и для XtraDB).
Как получить больше данных об ошибке
После получения ошибки выполните SHOW ENGINE INNODB STATUS и смотрите содержимое секции LATEST FOREIGN KEY ERROR. Этот способ имеет следующие недостатки:
- требует привилегии SUPER
- содержит информацию о последней ошибке, связанной с внешними ключами, из-за чего нужно выполнять SHOW ENGINE INNODB STATUS сразу после возникновения ошибки, что не всегда удобно/возможно
- используются внутренние имена таблиц (например, ‘test.#sql-d88_b’), что затрудняет диагностику
- порой содержит мало полезной информации или таковая вообще отсутствует.
Альтернатива: использовать MariaDB версий больше 5.5.45 и 10.0.21, в которых сообщения об ошибках значительно улучшены и указывают причину возникновения ошибки.
Errno 150
Если в сообщении об ошибке содержится errno 150 (или errno 121), значит парсер MySQL не смог распознать ошибку и передал команду (create/alter) на выполнение в InnoDB. В этом разделе перечислены ситуации, приводящие к ошибкам, содержащим errno 150.
А1. Нет индекса в родительской таблице. Набор полей, на которые ссылается дочерняя таблица, должен быть проиндексирован (или являться левой частью другого индекса). Порядок полей в индексе должен быть таким же как в определении внешнего ключа. Сюда же относится случай отсутствия нужной колонки в родительской таблице (нет колонки, нет и индекса).
Неочевидный момент: на колонке родительской таблицы есть индекс — полнотекстовый (fulltext). Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть обычным (btree).
Другой неочевидный момент: на колонке родительской таблицы есть индекс — префиксный. Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть определен на всей длине колонки.
Строго говоря, поля в дочерней таблице тоже должны быть проиндексированы, но если нет подходящего индекса, MySQL автоматически его создаст при добавлении внешнего ключа (в совсем уж древних версиях требовалось предварительное создание индекса).
Примеры
create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-16 06:37:39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key (a) references t1(b)) engine=innodb:
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-constr
aints.html for correct foreign key definition.
————
— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1
create table t2 (a int) engine=innodb;
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1005 (HY000): Cannot create table ‘test.#sql-a64_1’ (errno: 150)
— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1215 (HY000): Cannot add foreign key constraint
— аналогично и для оператора CREATE
drop table t2;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
Обратите внимание, если внешний ключ уже существует и в результате изменений (alter table) возникает ситуация отсутствия индекса в родительской таблице, то код ошибки будет 1025:
create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
alter table t1 drop a;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_5′ to ‘.testt1′ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220 7:14:25 Error in foreign key constraint of table test/t2:
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 «t2_ibfk_1» FOREIGN KEY («a») REFERENCES «t1» («a»)
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB: Renaming table `test`.`#sql-d6c_5` to `test`.`t1` failed!
———
А2. Родительская таблица не найдена в словаре данных InnoDB. Это означает, что родительская таблица должна существовать и быть постоянной InnoDB таблицей. Не временной InnoDB таблицей, так как информация о временных таблицах не сохраняется в словаре данных InnoDB. И уж тем более не представлением.
Примеры
mysql> create table t1 (a int, index(a)) engine=myisam;
mysql> create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-17 16:30:09 0x364c Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
————
А3. Синтаксическая ошибка. Внешние ключи реализованы на уровне хранилища, и в старых версиях парсер сервера MySQL не распознавал синтаксические ошибки внешних ключей, из-за чего их было трудно идентифицировать.
Примеры
Например, в определении внешнего ключа количество столбцов дочерней таблицы не совпадает с количеством столбцов родительской таблицы:
create table t1(id int not null primary key, b int, key(b)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)
alter table t1 add foreign key(id,b) references t1(id);
ERROR 1005 (HY000): Can‘t create table ‘test.#sql-d88_b’ (errno: 150)
show warnings;
+——-+——+—————————————————+
| Level | Code | Message |
+——-+——+—————————————————+
| Error | 1005 | Can‘t create table ‘test.#sql-d88_b’ (errno: 150) |
+——-+——+—————————————————+
— понять, что причина в синтаксической ошибке
— можно только из:
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22:28:23 Error in foreign key constraint of table test/#sql-d88_b:
foreign key(id,b) references t1(id):
Syntax error close to:
— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 (42000): Incorrect foreign key definition for ‘foreign key without name’: Key reference and table reference don‘t match
Другой пример: попробуем создать внешний ключ на поле типа text:
create table t1 (a text , index(a(50))) engine=innodb;
create table t2 (a text, foreign key (a) references t1(a)) engine=innodb;
ERROR 1170 (42000): BLOB/TEXT column ‘a’ used in key specification without a key length
— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:
create table t2 (a text, foreign key (a(50)) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.
А4. Несовпадение типов данных. Столбцы дочерней таблицы, входящие в определение внешнего ключа, должны иметь такие же типы данных, что и столбцы родительской таблицы, на которые они ссылаются, вплоть до атрибутов: знак и кодировка/сопоставление.
Примеры
— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 (a int unsigned, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 03:00:47 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
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-constr
aints.html for correct foreign key definition.
————
Если несоответствие типов данных возникает во время изменения таблицы при уже существующем внешнем ключе, то ошибка будет иметь вид:
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
MariaDB [test]> alter table t1 modify a int unsigned;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt1′ (errno: 150)
А5. Некорректно задано действие внешнего ключа. Если в определении внешнего ключа указано ON UPDATE SET NULL и/или ON DELETE SET NULL, то соответствующие столбцы дочерней таблицы не должны быть определены как NOT NULL.
Примеры
create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int not null, foreign key (a) references t1(a) on delete set null) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
— в старых версиях будет:
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 06:24:42 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a) on delete set null) engine=innodb:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
————
Если коллизия возникает при уже существующем внешнем ключе, то:
create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a) on delete set null) engine=innodb;
alter table t2 modify a int not null;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt2′ (errno: 150)
А6. Дочерняя таблица является временной InnoDB таблицей. Внешние ключи можно создавать только в постоянной, несекционированной InnoDB таблице.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create temporary table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 4:22:26 Error in foreign key constraint of table temp/#sql318_4_1:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— в новых версиях ошибка будет иметь вид:
ERROR 1215 (HY000): Cannot add foreign key constraint
А7. Родительская таблица является секционированной таблицей. На данный момент (MySQL 5.7 и MariaDB 10.1) внешние ключи не поддерживаются для секционированных таблиц (partitioned tables). Иными словами, ни родительская, ни дочерняя таблица не должны иметь секции. В случае, когда внешний ключ ссылается на секционированную таблицу диагностика ошибки затруднена ошибкой вывода show engine innodb status:
Примеры
create table t1 (a int, index(a)) partition by range (a)
(partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19:38:14 Error in foreign key constraint of table test/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)
— в новых версиях ошибка будет иметь вид:
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
Если разбивать на секции родительскую таблицу после создания внешнего ключа, то
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
alter table t1 PARTITION BY HASH(a) PARTITIONS 8;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR
Errno 121
Такой результат возникает только в одном случае.
Б1. Неуникальное имя ограничения. Обратите внимание: речь не о имени внешнего ключа. Если при создании внешнего ключа вы указываете не обязательное ключевое слово CONSTRAINT, то идущий после него идентификатор должен быть уникальным в пределах базы данных.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, CONSTRAINT q1 foreign key (a) references t1(a)) engine=innodb;
create table t3 (a int, CONSTRAINT q1 foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t3’ (errno: 121)
— в 5.7 будет другая ошибка
ERROR 1022 (23000): Cannot write; duplicate key in table ‘t3’
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 3:31:11 Error in foreign key constraint creation for table `test`.`t3`.
A foreign key constraint of name `test`.`q1`
already exists. (Note that internally InnoDB adds ‘databasename’
in front of the user-defined constraint name.)
Note that InnoDB FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
———
Нет ошибок
Внешний ключ не создается, и нет никаких ошибок. Это может происходить по следующим причинам:
В1. Дочерняя таблица не является InnoDB таблицей. В этом случае для совместимости с другими субд парсер MySQL просто проигнорирует конструкцию внешнего ключа.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=myisam;
Query OK, 0 rows affected (0.33 sec)
MariaDB [test]> show create table t2G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
В2. Не соответствует синтаксису MySQL. Стандарт SQL разрешает указывать внешний ключ сразу при объявлении колонки с помощью конструкции REFERENCES (например, … a int references t1(a), …), однако MySQL игнорирует такую форму записи. Единственный способ создать в нем внешний ключ — это использовать отдельный блок FOREIGN KEY:
[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 | SET DEFAULT
Несоответствие данных
В этой части собраны ошибки, которые возникают из-за нарушения ссылочной целостности, т.е. наличие в дочерней таблице записей, которым нет соответствия в родительской таблице.
Г1. Удаление родительской таблицы. Нельзя удалить родительскую таблицу при наличии внешнего ключа.
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
drop table t1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Удаление следует понимать в расширенном варианте как удаление из множества InnoDB таблиц. Например, если мы сменим (alter table) движок родительской таблицы на MyISAM, то с точки зрения ограничения внешнего ключа родительская таблица перестанет существовать (т.к. она должна быть постоянной innodb таблицей):
alter table t1 engine=myisam;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Сначала нужно удалить внешний ключ (или всю дочернюю таблицу, что удалит в том числе и внешний ключ). Если вы не знаете какие таблицы являются дочерними для заданной таблицы, то это можно определить через запрос к information_schema:
select table_name from information_schema.key_column_usage
where table_schema = «test» and references_table_name = «t1»;
Г2. Изменение данных в родительской таблице. Если в определении внешнего ключа не задано действие при update/delete, то такие операции над родительской таблицей могут привести к несогласованности данных, т.е. появлению в дочерней таблице записей не имеющих соответствия в родительской таблице.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
insert into t1 values(1);
insert into t2 values(1);
update t1 set a=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1`(`a`))
Г3. Изменение данных в дочерней таблице. Если insert/update записи в дочерней таблицы приводит к несогласованности данных, то
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
insert into t2 values(15);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
Г4. Добавление внешнего ключа на не пустую таблицу. При попытке добавить внешний ключ на таблицу, в которой есть записи, не удовлетворяющие условию внешнего ключа (т.е. не имеющие соответствия в родительской таблице), будет ошибка:
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, index(a)) engine=innodb;
insert into t2 values(2);
alter table t2 add foreign key (a) references t1(a);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-3f0_4`, CONSTRAINT `#sql-3f0_4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
Г5. Не уникальный ключ в родительской таблице. По стандарту SQL набор полей, на которые ссылается внешний ключ, должен быть уникальным. Однако, реализация внешних ключей в InnoDB позволяет иметь несколько «родителей». Из-за этого возникает трудно диагностируемая ошибка:
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, index(a)) engine=innodb;
insert into t1 values (1),(1);
insert into t2 values(1);
delete from t1 where a=1 limit 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1`(`a`))
Сводная таблица
По вертикали расположены коды ошибок MySQL, которые возникают при работе с внешними ключами («нет ошибок» соответствует ситуации, когда сервер не генерирует ошибку, но и не создает внешний ключ). По горизонтали — идентификаторы причин, которые могут привести к ошибке. Плюсы на пересечении указывают какие причины приводят к той или иной ошибке.
| А1 | А2 | А3 | А4 | А5 | А6 | А7 | Б1 | В1 | В2 | Г1 | Г2 | Г3 | Г4 | Г5 | |
| MySQL error 1005 | + | + | + | + | + | + | + | + | |||||||
| MySQL error 1022 | + | ||||||||||||||
| MySQL error 1025 | + | + | + | ||||||||||||
| MySQL error 1215 | + | + | + | + | + | ||||||||||
| MySQL error 1217 | + | + | |||||||||||||
| MySQL error 1239 | + | ||||||||||||||
| MySQL error 1451 | + | + | |||||||||||||
| MySQL error 1452 | + | + | |||||||||||||
| нет ошибок | + | + |
P.S. Если ваш случай не рассмотрен в статье, то задавайте вопрос на форуме SQLinfo. Вам ответят, а статья будет расширена.
Дата публикации: 2.12.2016
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
I am running into the same problem here. I have a migration that adds a foreign key, referencing the id column on the users table and after upgrading to Laravel 5.8 this no longer works. This is the up of my original migration:
public function up() { Schema::create('two_factor_auths', function (Blueprint $table) { $table->string('id')->nullable(); $table->unsignedInteger('user_id'); $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); $table->timestamps(); }); }
But then running php artisan migrate -v on a freshly created db (InnoDB) results in:
IlluminateDatabaseQueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)
at /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
660| // If an exception occurs when attempting to run a query, we'll format the error
661| // message to include the bindings with SQL, which will make this exception a
662| // lot more helpful to the developer instead of just the database's errors.
663| catch (Exception $e) {
> 664| throw new QueryException(
665| $query, $this->prepareBindings($bindings), $e
666| );
667| }
668|
Exception trace:
1 PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458
2 PDOStatement::execute()
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458
3 IlluminateDatabaseConnection::IlluminateDatabase{closure}("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [])
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657
4 IlluminateDatabaseConnection::runQueryCallback("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [], Object(Closure))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
5 IlluminateDatabaseConnection::run("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [], Object(Closure))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459
6 IlluminateDatabaseConnection::statement("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade")
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:97
7 IlluminateDatabaseSchemaBlueprint::build(Object(IlluminateDatabaseMySqlConnection), Object(IlluminateDatabaseSchemaGrammarsMySqlGrammar))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:264
8 IlluminateDatabaseSchemaBuilder::build(Object(IlluminateDatabaseSchemaBlueprint))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:165
9 IlluminateDatabaseSchemaBuilder::create("two_factor_auths", Object(Closure))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237
10 IlluminateSupportFacadesFacade::__callStatic("create")
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/michaeldzjap/twofactor-auth/src/database/migrations/2017_05_26_102832_create_two_factor_auths_table.php:21
11 CreateTwoFactorAuthsTable::up()
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:360
12 IlluminateDatabaseMigrationsMigrator::IlluminateDatabaseMigrations{closure}()
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:367
13 IlluminateDatabaseMigrationsMigrator::runMigration(Object(CreateTwoFactorAuthsTable), "up")
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:178
14 IlluminateDatabaseMigrationsMigrator::runUp("/home/vagrant/code/laravel-two-factor-authentication-example/vendor/michaeldzjap/twofactor-auth/src/database/migrations/2017_05_26_102832_create_two_factor_auths_table.php")
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:147
15 IlluminateDatabaseMigrationsMigrator::runPending([])
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:96
16 IlluminateDatabaseMigrationsMigrator::run([])
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:71
17 IlluminateDatabaseConsoleMigrationsMigrateCommand::handle()
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
18 call_user_func_array([])
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
19 IlluminateContainerBoundMethod::IlluminateContainer{closure}()
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
20 IlluminateContainerBoundMethod::callBoundMethod(Object(IlluminateFoundationApplication), Object(Closure))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
21 IlluminateContainerBoundMethod::call(Object(IlluminateFoundationApplication), [])
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/Container.php:580
22 IlluminateContainerContainer::call()
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
23 IlluminateConsoleCommand::execute(Object(SymfonyComponentConsoleInputArgvInput), Object(IlluminateConsoleOutputStyle))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Command/Command.php:255
24 SymfonyComponentConsoleCommandCommand::run(Object(SymfonyComponentConsoleInputArgvInput), Object(IlluminateConsoleOutputStyle))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
25 IlluminateConsoleCommand::run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:901
26 SymfonyComponentConsoleApplication::doRunCommand(Object(IlluminateDatabaseConsoleMigrationsMigrateCommand), Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:262
27 SymfonyComponentConsoleApplication::doRun(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:145
28 SymfonyComponentConsoleApplication::run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
29 IlluminateConsoleApplication::run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
/home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:122
30 IlluminateFoundationConsoleKernel::handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
/home/vagrant/code/laravel-two-factor-authentication-example/artisan:37
Changing from $table->unsignedInteger('user_id'); to $table->bigIncrements('user_id'); in my migration makes it work. I can then successfully run the migration. Maybe this will help.