MySQL Foreign Key Error[Solved] Error code 1215 & Error code 1452

In this blog, we’ll explain how to resolve MySQL Foreign key error code 1215: Cannot add foreign key constraint and Error code 1452: Cannot add or update a child row a foreign key constraint failed.

There are actually many reasons this can happen, we explain most common reasons why you can get MySQL Error Code 1215, and Error code 1452 how to diagnose your case to find which one is affecting you, and provide solutions for adding the foreign key.

MySQL doesn’t provide the exact root cause of Foreign key error message

 ERROR 1215 (HY000): Cannot add foreign key constraint 

 Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 

You need to run “show engine innodb status;” command to get exact root cause of the foreign key error. This command returns all InnoDB engine details with large content and needs to grep the “LAST FOREIGN KEY ERROR” session. It is a bit difficult. if You need easy solution Smart MySQL’s Workbench is only the tool that can provide foreign key errors with LAST FOREIGN KEY ERROR details.

SamrtMySQL’s SmartWorkbench is one of the best GUI tool for MySQL and you can develop SQL more than 10X compare to other GUI tools and you can optimize your SQL queries and Debug & Fix production issues.

There are two main reasons for getting Foreign key errors

1. Columns datatype miss-match or child table creating before parent table Error code 1215
2. Data miss-match Error code 1452

Error code 1215: Column Datatype miss-match

The Error 1512 occurs when you are trying to create new tables or adding new Foreign key constraints. If you are creating new table then you just check datatype if there is any difference between a parent table and a child table column. Ex:

CREATE TABLE departments ( dept_no char(4) NOT NULL, dept_name varchar(40) NOT NULL, test enum('', '1', '2') DEFAULT NULL, PRIMARY KEY (dept_no), UNIQUE KEY dept_name (dept_name) ); CREATE TABLE employee ( emp_no int(11) NOT NULL, dep_no INT DEFAULT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL, gender enum('M', 'F') NOT NULL, hire_date date NOT NULL, email varchar(100) DEFAULT NULL, salary int(11) DEFAULT NULL, PRIMARY KEY (emp_no), KEY dep_no (dep_no), CONSTRAINT employee_ibfk_1 FOREIGN KEY (dep_no) REFERENCES departments (dept_no) ON DELETE CASCADE ON UPDATE CASCADE ); Error Code 1215: Cannot add foreign key constraint

In the above example department table’s dept_no column datatype is char(4) but in child table employee dept_no it is INT datatype. Both data types are not the same. You must use the same data type in the child table employee table or bigger size the same datatype ex: you can use char(4) or char(10).

There is one more case this error can come. If you create the Employee table before the Department table. You must disable foreign_key_checks using the following MySQL command if you want to create a table in any order.

set foreign_key_checks=0;

Error code 1452: Data miss-match.

This error usually occurs when both tables have data then try to create a Foreign key and few child table column data do not exist in the parent table. Ex: Employee table has some dept_no which do not exist in Department table dep_no then if you try to add foreign key then it throws error code 1452. You can check such miss-match records using the following query

SELECT
   *
 FROM
   employee
 WHERE
   dep_no = 
       (
         SELECT
           d.dept_no
         FROM
           departments d
       ) ;

If the above query returns result set then there is a data miss match and you need to correct them to fix this issue. You need to change your table names and column names.

You can write such queries 10X faster. It took 15 sec to write that query with help of SmartMySQL workbench. . I just drag employee and department table and do some small modifications using SmartMySQL workbench. It is free you can start to use if you need to write queries in seconds

There is a more very good blog from percona about foreign key error 1542 Let me know you if there are any other possible causes in the below comments.