Error code 1215 cannot add foreign key constraint mysql

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.

Error Message:

Error Code: 1215. Cannot add foreign key constraint

Example:

Error Code: 1215. Cannot add foreign key constraint

Possible Reason:

Case 1: MySQL storage engine.

MySQL supports several storage engines, comparing features of the different mysql storage engines are given below. Note that only InnoDB storage engine supports foreign key, when you are using different mysql storage engine you may get the error code: 1215 cannot add foreign key constraint.

MySQL STORAGE ENGINE FEATURES

Case 2: Key does not exist in the parent table.

When you are trying to reference a key on the parent table which is not exist, you may likely get the error code: 1215 cannot add foreign key constraint. When you are trying to reference a key on parent table which is not a candidate key (either a primary key or a unique key) you may get the error code: 1215 cannot add foreign key constraint. According to definition a foreign key must reference a candidate key of some table. It does not necessarily to be a primary key. MySQL requires index on corresponding referenced key, so you need a unique key.

Case 3: Foreign key definition.

When the definition of the foreign key is different from the reference key, you may get the error code: 1215 cannot add foreign key constraint. The size and sign of the integer must be the same. The character string columns, the character set and collation must be the same. Otherwise you may get the error code: 1215 cannot add foreign key constraint. The length of the string types need not be the same.

Case 4: Foreign key as a primary key.

When you are using composite primary key or implementing one-to-one relationships you may using foreign key as a primary key in your child table. In that case, definition of foreign key should not define as ON DELETE SET NULL. Since primary key cannot be NULL, defining the referential action in such a way may produce the error code: 1215 cannot add foreign key constraint.

Case 5: Referential action – SET NULL.

When you specify SET NULL action and you defined the columns in the child table as NOT NULL, you may get the error code: 1215 cannot add foreign key constraint.

Solution:

Case 1: Storage Engine.

Only MySQL storage engine InnoDB supports foreign key, make sure you are using InnoDB storage engine. You can use the following command to determine which storage engine your server supports.

To determine the storage engine used in the corresponding table, you can run the following command:

MySQL allows you to define storage engine on table level, you can assign the storage engine by using the following statement:

Example:

To alter the storage engine of an existing table, you can run the following statement:

Case 2: Key does not exist in the parent table.

Make sure your parent table contains at least one key to which you are going to create a reference key.

If the key does not present in the table, you can create a new key by using following statement:

Case 3: Foreign key definition.

The data type must be same for both the foreign key and referenced key. The size and sign of integer types must be the same. For character strings the character set and collation must be the same.

Consider the following example to understand this case:

Note that, in the above example, the data type of the id in student table is TINYINT but the data type of the student_id column in book table which referencing the student table.

Here you need to alter the data type of the student_id column in book table. You can alter the data type of the existing column using following statement:

mysql > ALTER TABLE book MODIFY COLUMN  Id TINY INT NOT NULL ;

 After altering the required fields, the new statement may look as follows:

Case 4: Foreign key as a primary key.

When you are implementing one-to-one relationship or composite primary key you may use foreign key as a primary key in your child table. Definition of foreign key should not define as ON DELETE SET NULL. Since primary key cannot be NULL. The following example will illustrate this case better:

Case 5: Referential action – SET NULL.

Make sure when you specify SET NULL action, define the columns in the child table as NOT NULL.

The following example will explain this case clearly:

You can solve this by altering the foreign key column from not null to null. You can do that by using following statement:

mysql > ALTER TABLE book MODIFY reg_no varchar(255) ;

After modifying the table, the new statement may look similar to as follows:




CREATE TABLE student(
                  id INT NOT NULL, 
                  Reg_no varchar (255),
                  Key (Reg_no),            
                  PRIMARY KEY (id)
)ENGINE=INNODB;



  


                       
  




CREATE TABLE book
(
                  book_id INT, 
                  reg_no varchar(255) NULL,       /* allowed NULL*/
                  FOREIGN KEY (reg_no) 
                  REFERENCES student(reg_no)
                  ON DELETE SET NULL              /*Action specified as SET NULL*/
) ENGINE=INNODB;




I hope this post will help you to solve the mysql error code: 1215 cannot add foreign key constraint. If you still couldn’t figure out the issue, get in touch with me through contact me page, I will help you to solve this issue.

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. 

[Solved-6 Solutions] MySQL Error 1215: Cannot add foreign key constraint

MySQL Error 1215: Cannot add foreign key constraint

SET @[email protected]@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SOURCE /backups/mydump.sql; -- restore your backup within THIS session
SET [email protected]_FOREIGN_KEY_CHECKS;

mysql> CREATE TABLE child (
    ->   id INT(10) NOT NULL PRIMARY KEY,
    ->   parent_id INT(10),
    ->   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
    -> ) ENGINE INNODB;
ERROR 1215 (HY000): Cannot add foreign key constraint
# We check for the parent table and is not there.
mysql> SHOW TABLES LIKE 'par%';
Empty set (0.00 sec)
# We go ahead and create the parent table (we’ll use the same parent table structure for all other example in this blogpost):
mysql> CREATE TABLE parent (
    ->   id INT(10) NOT NULL PRIMARY KEY,
    ->   column_1 INT(10) NOT NULL,
    ->   column_2 INT(10) NOT NULL,
    ->   column_3 INT(10) NOT NULL,
    ->   column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
    ->   KEY column_2_column_3_idx (column_2, column_3),
    ->   KEY column_4_idx (column_4)
    -> ) ENGINE INNODB;
Query OK, 0 rows affected (0.00 sec)
# And now we re-attempt to create the child table
mysql> CREATE TABLE child (
    ->   id INT(10) NOT NULL PRIMARY KEY,drop table child;
    ->   parent_id INT(10),
    ->   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
    -> ) ENGINE INNODB;
Query OK, 0 rows affected (0.01 sec)

# wrong; single pair of backticks wraps both table and column
ALTER TABLE child  ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`;
# correct; one pair for each part
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`);
# also correct; no backticks anywhere
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);
# also correct; backticks on either object (in case it’s a keyword)
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);

# wrong; Parent table name is ‘parent’
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id);
# correct
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);

# wrong; id column in parent is INT(10)
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_id BIGINT(10) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
) ENGINE INNODB;
# correct; id column matches definition of parent table
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_id INT(10) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
) ENGINE INNODB;

# wrong; column_1 is not indexed in our example table
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_column_1 INT(10),
  FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`)
) ENGINE INNODB;
# correct; we first add an index and then re-attempt creation of child table
ALTER TABLE parent ADD INDEX column_1_idx(column_1);
# and then re-attempt creation of child table
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_column_1 INT(10),
  FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`)
) ENGINE INNODB;

# wrong; column_3 only appears as the second part of an index on parent table
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_column_3 INT(10),
  FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`)
) ENGINE INNODB;
# correct; create a new index for the referenced column
ALTER TABLE parent ADD INDEX column_3_idx (column_3);
# then re-attempt creation of child
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_column_3 INT(10),
  FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`)
) ENGINE INNODB;

# wrong; the parent table uses utf8/utf8_bin for charset/collation
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`)
) ENGINE INNODB;
# correct; edited DDL so COLLATE matches parent definition
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
  FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`)
) ENGINE INNODB;

# wrong; the parent table in this example is MyISAM:
CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY
) ENGINE MyISAM;
# correct: we modify the parent’s engine
ALTER TABLE parent ENGINE=INNODB;

# wrong; only parent table name is specified in REFERENCES
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  column_2 INT(10) NOT NULL,
  FOREIGN KEY (column_2) REFERENCES parent
) ENGINE INNODB;
# correct; both the table and column are in the REFERENCES definition
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  column_2 INT(10) NOT NULL,
  FOREIGN KEY (column_2) REFERENCES parent(column_2)
) ENGINE INNODB;

# wrong: the parent table we see below is using PARTITIONs
CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY
) ENGINE INNODB
PARTITION BY HASH(id)
PARTITIONS 6;
#correct: ALTER parent table to remove partitioning
ALTER TABLE parent REMOVE PARTITIONING;

# wrong; this parent table has a generated virtual column
CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY,
  column_1 INT(10) NOT NULL,
  column_2 INT(10) NOT NULL,
  column_virt INT(10) AS (column_1 + column_2) NOT NULL,
  KEY column_virt_idx (column_virt)
) ENGINE INNODB;
# correct: make the column STORED so it can be used as a foreign key
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL;
# And now the child table can be created pointing to column_virt
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_virt INT(10) NOT NULL,
  FOREIGN KEY (parent_virt) REFERENCES parent(column_virt)
) ENGINE INNODB;

# wrong; this parent table has a generated virtual column
CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY,
  column_1 INT(10) NOT NULL,
  column_2 INT(10) NOT NULL,
  column_virt INT(10) AS (column_1 + column_2) NOT NULL,
  KEY column_virt_idx (column_virt)
) ENGINE INNODB;
# correct: make the column STORED so it can be used as a foreign key
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL;
# And now the child table can be created pointing to column_virt
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_virt INT(10) NOT NULL,
  FOREIGN KEY (parent_virt) REFERENCES parent(column_virt)
) ENGINE INNODB;

Error Code: 1215. Cannot add foreign key constraint

Error Code: 1215. Cannot add foreign key constraint

Case 1: MySQL storage engine.

Learn mysql — mysql tutorial — mysql address — mysql examples — mysql programs

Case 2: Key does not exist in the parent table.

Case 3: Foreign key definition.

Case 4: Foreign key as a primary key.

Case 5: Referential action — SET NULL.

Case 1: Storage Engine.

mysql > SHOW ENGINES G

mysql > SHOW CREATE TABLE table_name;

mysql > CREATE TABLE table_name (id INT) ENGINE = INNODB;

CREATE TABLE student (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

mysql > ALTER TABLE table_name ENGINE = INNODB;

Case 2: Key does not exist in the parent table.

mysql > SHOW CREATE TABLE table_name;

mysql > ALTER TABLE table_name ADD Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

mysql > ALTER TABLE table_name ADD CONSTRAINT constr_ID UNIQUE (column_name);

Case 3: Foreign key definition.

CREATE TABLE student
(           
id TINYINT NOT NULL,                /* note the data type*/                  
PRIMARY KEY (id)
) ENGINE=INNODB; 

CREATE TABLE book
(                  
Id INT,
student_id INT,         /* data type different from the referencing data field*/                  
INDEX stu_ind (student_id),                  
FOREIGN KEY (student_id)
REFERENCES student(id)                  
ON DELETE CASCADE
) ENGINE=INNODB;

mysql > ALTER TABLE book MODIFY COLUMN  Id TINY INT NOT NULL ;

CREATE TABLE student 
(                   
id TINYINT NOT NULL,                /* note the data type*/                  
PRIMARY KEY (id)
) ENGINE=INNODB; 

CREATE TABLE book
(                  
id INT,
student_id TINYINT NOT NULL,                   /* data type same as the referencing data field*/      INDEX stu_ind (student_id),                  
FOREIGN KEY (student_id)
REFERENCES student(id)                  
ON DELETE CASCADE
) ENGINE=INNODB;

Case 4: Foreign key as a primary key.

CREATE TABLE user 
(                  
user_id INT NOT NULL,PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE student
(                  
user_id INT NOT NULL,             
PRIMARY KEY (user_id),           
FOREIGN KEY (user_id),                  
REFERENCES user (user_id),                  
ON DELETE CASCADE        /* Referential Action - ON DELETE not SET NULL */
) ENGINE=INNODB;

Case 5: Referential action — SET NULL.

CREATE TABLE student
 (                   
id INT NOT NULL,
Reg_no varchar (255),                  
Key (Reg_no),          
PRIMARY KEY (id)
) ENGINE=INNODB; 

CREATE TABLE book
(                  
book_id INT,
reg_no varchar(255) NOT NULL,                 /* defined as NOT NULL*/                  
FOREIGN KEY (reg_no)
REFERENCES student(reg_no)                  
ON DELETE SET NULL                                        /*Action specified as SET NULL*/
) ENGINE=INNODB;

mysql > ALTER TABLE book MODIFY reg_no varchar(255) ;

CREATE TABLE student(
                  id INT NOT NULL,
                  Reg_no varchar (255),
                  Key (Reg_no),            
                  PRIMARY KEY (id)
)ENGINE=INNODB;
CREATE TABLE book
(
                  book_id INT, 
                  reg_no varchar(255) NULL,       /* allowed NULL*/
                  FOREIGN KEY (reg_no) 
                  REFERENCES student(reg_no)
                  ON DELETE SET NULL              /*Action specified as SET NULL*/
) ENGINE=INNODB;

...
PRIMARY KEY (`id`),
FOREIGN KEY (`id`) REFERENCES `t` (`other_id`) ON DELETE SET NULL
....

SELECT * FROM information_schema.columns WHERE 
TABLE_NAME IN (tb_name','referenced_table_name') AND 
COLUMN_NAME  IN ('col_name','referenced_col_name')G

same COLUMN_TYPE(length), same COLATION

set foreign_key_checks=0;
ALTER TABLE tb_name ADD FOREIGN KEY(col_name) REFERENCES ref_table(ref_column) ON DELETE ...
set foreign_key_checks=1;

Databases operations like backup or restore often result in annoying errors.

Usually, MySQL error 1215 pops up while creating a foreign key or constraints in the database table.

There are many reasons for this error to occur which include in-appropriate quotes in the constraints, missing table, parent table doesn’t use InnoDB and many more.

At Bobcares, we receive requests on MySQL errors as a part of our Server Management Services.

Today, let’s see what causes MySQL error 1215 and how our Support Engineers fix it.

What causes MySQL error 1215?

Let’s have a first look at the MySQL error 1215. Recently one of our customers reported a MySQL error. He was trying to create a relational database table with 3 primary keys. But, this resulted in the error:

There can be many causes for this error to occur. Let’s now check out few causes that our Database Experts usually see in databases.

1. The table does not exist

Sometimes, the database table to which customers try to add constraints doesn’t exist. The results in 1215 error. However, there will be no hint given that the table doesn’t exist. So its advisable to check the available tables in the database using the command:

show tables;

Through this, we can confirm if the tables are available or not.

2. Parent table is not using InnoDB

If the storage engine of the parent table is not InnoDB then also it will throw errors. So it is necessary to check the engine of the table. And then we can set it to InnoDB if it not set.

We use the below command to change the storage engine of the tables in case if there is any change required.

ALTER TABLE parent ENGINE=INNODB;

Ideally, our Support Engineers suggest customers make sure of using InnoDB as the engine on all tables in the database.

3. In-appropriate quotes to the tables in constraints

Normally, while running commands to alter the tables wrong quotes will be provided. This will run into problems and finally end up with a MySQL error 1215.

Thus, it is really important to avoid any syntax errors while executing MySQL queries.

4. A typo in the local key and foreign table

All the details in both the local key and foreign tables must have the same variables. If there are any typo errors while running the command then the final result will end into an error. Again, if the Primary key or Foreign Key is varchar, we always make sure that the collation is the same for both.

How we fix MySQL error 1215?

We have now discussed the different causes of this error message.

Let’s see how our Support Engineers fix this error.

1. Check for the table availability

Our Support Engineers started troubleshooting the error by checking if the table is available. We used the below command for it.

show tables;

We found that the table was available to create the constraint,

2. Check if the parent table is using InnoDB

We further continued to investigate by checking the storage engine set to the database table. We used the below command for it.

SHOW CREATE TABLE parent

As a result, we found that the storage engine was not InnoDB. It was set to ENGINE=MYISAM. So we changed the storage engine using the command:

ALTER TABLE parent ENGINE=INNODB;

Finally, this fixed the error.

3. Check if there are any in-appropriate quotes

We also confirm if our customers are running the right command. We check all the details like the quotes in the command used as well.

Here is an example, where our customer used the quotes incorrectly in the command and ran into problems.

ALTER TABLE child ADD FOREIGN KEY (parentid) REFERENCES `parent(id)`;

We corrected the command and provided the right one below.

ALTER TABLE child ADD FOREIGN KEY (parentid) REFERENCES `parent`(`id`);

Also, it can be without quotes as below.

ALTER TABLE child ADD FOREIGN KEY (parentid) REFERENCES parent(id);

Lastly, running the right command fixed the error.

4. Verifying the existence of key

While entering the MySQL commands it is necessary to enter the right command.  We cross-check and see if the customer is trying to reference a nonexistent key on the target table.

Therefore, we correct the customers to use the key that really exists on the table. In some cases, the column in use for ON DELETE SET NULL may not be defined to be null. So we make sure that the column is set default null. This prevents the MySQL error 1215.

[Need any assistance with MySQL errors? – We’ll help you]

Conclusion

In short, the MySQL error 1215 is caused due to many reasons which mainly include in-appropriate quotes in the command, non-existence of the table in the constraint, parent table not using InnoDB and many more. Today, we saw the different causes and how our Support Engineers fix it.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Дата: 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 автоматически его создаст при добавлении внешнего ключа (в совсем уж древних версиях требовалось предварительное создание индекса).

А2. Родительская таблица не найдена в словаре данных InnoDB. Это означает, что родительская таблица должна существовать и быть постоянной InnoDB таблицей. Не временной InnoDB таблицей, так как информация о временных таблицах не сохраняется в словаре данных InnoDB. И уж тем более не представлением.

А3. Синтаксическая ошибка. Внешние ключи реализованы на уровне хранилища, и в старых версиях парсер сервера MySQL не распознавал синтаксические ошибки внешних ключей, из-за чего их было трудно идентифицировать.

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

А5. Некорректно задано действие внешнего ключа. Если в определении внешнего ключа указано ON UPDATE SET NULL и/или ON DELETE SET NULL, то соответствующие столбцы дочерней таблицы не должны быть определены как NOT NULL.

А6. Дочерняя таблица является временной InnoDB таблицей. Внешние ключи можно создавать только в постоянной, несекционированной InnoDB таблице.

А7. Родительская таблица является секционированной таблицей. На данный момент (MySQL 5.7 и MariaDB 10.1) внешние ключи не поддерживаются для секционированных таблиц (partitioned tables). Иными словами, ни родительская, ни дочерняя таблица не должны иметь секции. В случае, когда внешний ключ ссылается на секционированную таблицу диагностика ошибки затруднена ошибкой вывода show engine innodb status:

Errno 121

Такой результат возникает только в одном случае.

Б1. Неуникальное имя ограничения. Обратите внимание: речь не о имени внешнего ключа. Если при создании внешнего ключа вы указываете не обязательное ключевое слово CONSTRAINT, то идущий после него идентификатор должен быть уникальным в пределах базы данных.

Нет ошибок

Внешний ключ не создается, и нет никаких ошибок. Это может происходить по следующим причинам:

В1. Дочерняя таблица не является InnoDB таблицей. В этом случае для совместимости с другими субд парсер MySQL просто проигнорирует конструкцию внешнего ключа.

В2. Не соответствует синтаксису MySQL. Стандарт SQL разрешает указывать внешний ключ сразу при объявлении колонки с помощью конструкции REFERENCES (например, … a int references t1(a), …), однако MySQL игнорирует такую форму записи. Единственный способ создать в нем внешний ключ — это использовать отдельный блок FOREIGN KEY:

Несоответствие данных

В этой части собраны ошибки, которые возникают из-за нарушения ссылочной целостности, т.е. наличие в дочерней таблице записей, которым нет соответствия в родительской таблице.

Г1. Удаление родительской таблицы. Нельзя удалить родительскую таблицу при наличии внешнего ключа.

Удаление следует понимать в расширенном варианте как удаление из множества 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, то такие операции над родительской таблицей могут привести к несогласованности данных, т.е. появлению в дочерней таблице записей не имеющих соответствия в родительской таблице.

Г3. Изменение данных в дочерней таблице. Если insert/update записи в дочерней таблицы приводит к несогласованности данных, то

Г4. Добавление внешнего ключа на не пустую таблицу. При попытке добавить внешний ключ на таблицу, в которой есть записи, не удовлетворяющие условию внешнего ключа (т.е. не имеющие соответствия в родительской таблице), будет ошибка:

Г5. Не уникальный ключ в родительской таблице. По стандарту SQL набор полей, на которые ссылается внешний ключ, должен быть уникальным. Однако, реализация внешних ключей в InnoDB позволяет иметь несколько «родителей». Из-за этого возникает трудно диагностируемая ошибка:

Сводная таблица

По вертикали расположены коды ошибок MySQL, которые возникают при работе с внешними ключами («нет ошибок» соответствует ситуации, когда сервер не генерирует ошибку, но и не создает внешний ключ). По горизонтали — идентификаторы причин, которые могут привести к ошибке. Плюсы на пересечении указывают какие причины приводят к той или иной ошибке.

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.