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


Error Description:

MySQL Error 1215: Cannot add foreign key constraint

Solution 1:

  • MySQL never tells us exactly WHY it failed. There’s actually a multitude of reasons this can happen. Th below list is a compendium of the most common reasons why we can get ERROR 1215, how to diagnose our case to find which one is affecting us and potential solutions for adding the foreign key.

1) The Table or Index the Constraint Refers to Does Not Exist yet (Usual When Loading Dumps)

  • How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If we get error 1146 for any of them, it means tables are being created in wrong order.
  • How to fix: Run the missing CREATE TABLE and try again, or temporarily disable foreign-key-checks. This is especially needed during backup restores where circular references might exist. Simply run:
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SOURCE /backups/mydump.sql; -- restore your backup within THIS session
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
click below button to copy the code. By - mysql tutorial - team

Example: 

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)
click below button to copy the code. By - mysql tutorial - team

2) The Table or Index in the Constraint References Misuses Quotes

  • How to diagnose: Inspect each FOREIGN KEY declaration and make sure we either have no quotes around object qualifiers, or that we have quotes around the table and a SEPARATE pair of quotes around the column name.
  • How to fix: Either don’t quote anything, or quote the table and the column separately.

Example: 

# 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`);
click below button to copy the code. By - mysql tutorial - team

3) The Local Key, Foreign Table or Column in the Constraint References Have a Typo

  • How to diagnose: Run SHOW TABLES and SHOW COLUMNS and compare strings with those in our REFERENCESdeclaration.
  • How to fix: Fix the typo once we find it.

Example: 

# 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);
click below button to copy the code. By - mysql tutorial - team

4) The Column the Constraint Refers to Is Not of the Same Type or Width as the Foreign Column

  • How to diagnose: Use SHOW CREATE TABLE parent to check that the local column and the referenced column both have same data type and width.
  • How to fix: Edit our DDL statement such that the column definition in the child table matches that of the parent table.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

5) The Foreign Object Is Not a KEY of Any Kind

  • How to diagnose: Use SHOW CREATE TABLE parent to check that if the REFERENCES part points to a column, it is not indexed in any way.
  • How to fix: Make the column a KEY, UNIQUE KEY or PRIMARY KEY on the parent.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

6) The Foreign Key Is a Multi-Column PK or UK, Where the Referenced Column Is Not the Leftmost One

  • How to diagnose: Do a SHOW CREATE TABLE parent to check if the REFERENCES part points to a column that is present in some multi-column index(es), but is not the leftmost one in its definition.
  • How to fix: Add an index on the parent table where the referenced column is the leftmost (or only) column.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

7) Different Charsets/Collations Among the Two Table/Columns

  • How to diagnose: Run SHOW CREATE TABLE parent and compare that the child column (and table) CHARACTER SET and COLLATE parts match those of the parent table.
  • How to fix: Modify the child table DDL so that it matches the character set and collation of the parent table/column (or ALTER the parent table to match the child’s wanted definition.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

8) The Parent Table Is Not Using InnoDB

  • How to diagnose: Run SHOW CREATE TABLE parent and verify if ENGINE=INNODB or not.
  • How to fix: ALTER the parent table to change the engine to InnoDB.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

9) Using Syntax Shorthands to Reference the Foreign Key

  • How to diagnose: Check if the REFERENCES part only mentions the table name, MySQL doesn’t support this shortcut (even though this is valid SQL).
  • How to fix: Edit the child table DDL so that it specifies both the table and the column.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

10) The Parent Table Is Partitioned

  • How to diagnose: Run SHOW CREATE TABLE parent and find out if it’s partitioned or not.
  • How to fix: Removing the partitioning (i.e., merging all partitions back into a single table) is the only way to get it working.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

11) Referenced Column Is a Generated Virtual Column (This Is Only Possible With 5.7 and Newer)

  • How to diagnose: Run SHOW CREATE TABLE parent and verify that the referenced column is not a virtual column.
  • How to fix: CREATE or ALTER the parent table so that the column will be stored and not generated.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

12) Using SET DEFAULT for a Constraint Action

  • How to diagnose: Check our child table DDL and see if any of our constraint actions (ON DELETE, ON UPDATE) try to use SET DEFAULT
  • How to fix: Remove or modify actions that use SET DEFAULT from the child table CREATE or ALTERstatement.

Example: 

# 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;
click below button to copy the code. By - mysql tutorial - team

Solution 2:

  • Error Message:
Error Code: 1215. Cannot add foreign key constraint
click below button to copy the code. By - mysql tutorial - team

Example: 

Error Code: 1215. Cannot add foreign key constraint
click below button to copy the code. By - mysql tutorial - team

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 we are using different mysql storage engine we may get the error code: 1215 cannot add foreign key constraint.
mysql address

Learn mysql - mysql tutorial - mysql address - mysql examples - mysql programs

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

  • When we are trying to reference a key on the parent table which is not exist, we may likely get the error code: 1215 cannot add foreign key constraint. When we are trying to reference a key on parent table which is not a candidate key (either a primary key or a unique key) we 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 we need a unique key.

Case 3: Foreign key definition.

  • When the definition of the foreign key is different from the reference key, we 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 we 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 we are using composite primary key or implementing one-to-one relationships we may using foreign key as a primary key in our 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 we specify SET NULL action and we defined the columns in the child table as NOT NULL, we may get the error code: 1215 cannot add foreign key constraint.

Solution 3:

Case 1: Storage Engine.

  • Only MySQL storage engine InnoDB supports foreign key, make sure we are using InnoDB storage engine. We can use the following command to determine which storage engine our server supports.
mysql > SHOW ENGINES \G
click below button to copy the code. By - mysql tutorial - team
  • To determine the storage engine used in the corresponding table, we can run the following command:
mysql > SHOW CREATE TABLE table_name;
click below button to copy the code. By - mysql tutorial - team
  • MySQL allows us to define storage engine on table level, we can assign the storage engine by using the following statement:
mysql > CREATE TABLE table_name (id INT) ENGINE = INNODB;
click below button to copy the code. By - mysql tutorial - team

Example: 

CREATE TABLE student (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
click below button to copy the code. By - mysql tutorial - team
  • To alter the storage engine of an existing table, we can run the following statement:
mysql > ALTER TABLE table_name ENGINE = INNODB;
click below button to copy the code. By - mysql tutorial - team

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

  • Make sure our parent table contains at least one key to which we are going to create a reference key.
  • we can use the following statement to check the definition of a table:
mysql > SHOW CREATE TABLE table_name;
click below button to copy the code. By - mysql tutorial - team
  • If the key does not present in the table, we can create a new key by using following statement:
  • If our table does not have unique column, create a new unique data field and set it as unique so that we can have our existing data by using the following statement:
mysql > ALTER TABLE table_name ADD Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
click below button to copy the code. By - mysql tutorial - team
  • If the existing table contains the unique data field we can assign it as unique key, by using following statement:
mysql > ALTER TABLE table_name ADD CONSTRAINT constr_ID UNIQUE (column_name);
click below button to copy the code. By - mysql tutorial - team

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:
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;
click below button to copy the code. By - mysql tutorial - team
  • 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 we need to alter the data type of the student_id column in book table. we can alter the data type of the existing column using following statement:
mysql > ALTER TABLE book MODIFY COLUMN  Id TINY INT NOT NULL ;
click below button to copy the code. By - mysql tutorial - team
  • After altering the required fields, the new statement may look as follows:
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;
click below button to copy the code. By - mysql tutorial - team

Case 4: Foreign key as a primary key.

  • When we are implementing one-to-one relationship or composite primary key we may use foreign key as a primary key in our 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:
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;
click below button to copy the code. By - mysql tutorial - team

Case 5: Referential action - SET NULL.

  • Make sure when we specify SET NULL action, define the columns in the child table as NOT NULL.
  • The following example will explain this case clearly:
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;
click below button to copy the code. By - mysql tutorial - team
  • we can solve this by altering the foreign key column from not null to null. we can do that by using following statement:
mysql > ALTER TABLE book MODIFY reg_no varchar(255) ;
click below button to copy the code. By - mysql tutorial - team
  • 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;
click below button to copy the code. By - mysql tutorial - team

Solution 4:

  • Reasons we may get a foreign key constraint error:
    1. We are not using InnoDB as the engine on all tables.
    2. We are trying to reference a nonexistent key on the target table. Make sure it is a key on the other table (it can be a primary or unique key)
    3. The types of the columns are not the same (exception is the column on the referencing table can be nullable).

    Update:

  • One of the reasons may also be that the column you are using for ON DELETE SET NULL is not defined to be null. So make sure that the column is set default null.

Solution 5:

  • Error 1215 is an annoying one. Explosion Pill's answer covers the basics. we want to make sure to start from there. However, there are more, much more subtle cases to look out for:
  • For example, when we try to link up PRIMARY KEYs of different tables, make sure to provide proper ON UPDATE and ON DELETE options. E.g.:
...
PRIMARY KEY (`id`),
FOREIGN KEY (`id`) REFERENCES `t` (`other_id`) ON DELETE SET NULL
....
click below button to copy the code. By - mysql tutorial - team
  • PRIMARY KEYs (such as id) can't be NULL.
  • There are even more, similarly subtle issues when adding these sort of constraints, which is why when coming across constraint errors, always make sure that the constraints and their implications make sense in our current context.

Solution 6:

  • For MySQL (INNODB) ... get definitions for columns we want to link
SELECT * FROM information_schema.columns WHERE 
TABLE_NAME IN (tb_name','referenced_table_name') AND 
COLUMN_NAME  IN ('col_name','referenced_col_name')\G
click below button to copy the code. By - mysql tutorial - team
  • compare and verify both column definitions have

same COLUMN_TYPE(length), same COLATION

  • Could be helpfull to play like
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;
click below button to copy the code. By - mysql tutorial - team

Related Searches to MySQL Error 1215: Cannot add foreign key constraint

#1215 - cannot add foreign key constraint phpmyadmin1215 cannot add foreign key constraint laravelmysql error 1215 (hy000) cannot add foreign key constrainterror 1215 cannot add foreign key constraint mysql workbenchcannot add foreign key constraint error code 1215cannot resolve table name close tosql error (1215): cannot add foreign key constraintcannot add foreign key constraint djangocannot add foreign key constraint error code 1215 laravelgeneral error: 1215 cannot add foreign key constraint$table->engine = 'innodb';laravel migration orderlaravel add foreign key to existing tablemagento add foreign key constraint$table- engine = 'innodb' laravel

Summer Offline Internship

Summer Online Internship

Internship in Chennai

Programming / Technology Internship in Chennai

Wikitechy Rated 5 / 5 based on 378284 reviews.
Wikitechy-logo

World's No 1 Animated self learning Website with Informative tutorials explaining the code and the choices behind it all.

Wikitechy

About Us
Terms of Use
Privacy Policy
Contact Us

Workshop

Webinar

Join our Community

Advertise

Other Languages

English
Chinese
Deutsch
Russian

© 2016 - 2024 KaaShiv InfoTech, All rights reserved. Powered by Inplant Training in chennai | Internship in chennai