How to solve Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails ?

Suppose you have two table one is child table (profile) and second is parent table (login).You can get this error  when setting a foreign key to login. loginId to profile.fk_loginId column if the profile.fk_loginId (child.column) has a values  that not present in login. loginId  (parent.column) .So you would need to ensure that each profile.fk_loginId (child.column) has NULL or has values that present in login. loginId (parent.column).

You can check thsi by this Query:

select  fk_loginId  from  profile  left join login  on profile.fk_loginId=login.loginId where login.loginId is null;

Solution 1: (in Linux)

First open Terminal  (Ctrl + Alt + T).Then write there commands.

Here    root = mysql username , databasename = your database name.

:$ mysql  -u  root  -p

Enter password:

mysql> use  databasename

mysql> SET foreign_key_checks = 0;

mysql> ALTER TABLE profile ADD CONSTRAINT FK_profile_1 FOREIGN KEY FK_profile_1 (fk_loginId)

->     REFERENCES login (loginId)

->     ON DELETE RESTRICT

->      ON UPDATE RESTRICT;

OK, 13 rows affected (0.25 sec) Records: 13  Duplicates: 0  Warnings: 0

mysql> SET foreign_key_checks = 1;

Solution 2:

You have to  correct  mismatch values, tried again and problem will be solved.

Share on Google Plus

About JK STACK

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.