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

MySQL Foreign key Error 4X

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.

SmartMySQL Workbench displays MySQL Foreign key Exact Root cause

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 dep_no column datatype is char(4) but in child table employee dept_no it is INT datatype. Both datatypes 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 move 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 hlep 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

MySQL Development 10 X faster using SmartMySQL Workbench

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.

Tags: cannot add foreign key constraint mysql, cannot add foreign key constraint spring boot, er_no_referenced_row_2 mysql, error 1064 mysql workbench, Error code 1215, error code 1215 cannot add foreign key constraint 0 282 sec, error code 1215 mysql, Error code 1452, error code 3780 mysql, error in mysql, failed to add foreign key constraint, foreign key constraint error, foreign key not working in mysql, general error 1215 cannot add foreign key constraint laravel, heidisql error 1215, how to add foreign key in wamp server, how to remove foreign key constraint in mysql, how to solve a foreign key constraint fails, incorrect options in foreign key constraint, last_sql_errno 1032, mariadb cannot add foreign key, mysql add foreign key, mysql add foreign key constraint, mysql error 1215 hy000 cannot add foreign key constraint, MySQL Foreign Key Error, mysql foreign key example, mysql import foreign key constraint fails, mysql replication error_code 1452, mysql workbench foreign key, mysql workbench foreign key not working, on delete cascade on update cascade, on delete no action on update no action, phpmyadmin foreign key, relation could not be added phpmyadmin, result 2 when explaining filename, set foreign_key_checks=0, sql error 1452 sqlstate 23000 hibernate