MYSQL error code 1451: cannot delete or update parent row: a foreign key contraint fails

Question

Here are my tables:

create table caddy( 
Pnr char(13),
tips varchar(20),
name varchar(20),
primary key(Pnr)
)engine innodb;

create table golfbag( 
brand varchar(20),
type varchar(20),
spelarePnr char(13),
caddyPnr char(13),
primary key(brand, spelarePnr),
foreign key(spelarePnr) references spelare(Pnr),
foreign key(caddyPnr) references caddy(Pnr)
)engine innodb;

as you can see, there's join between caddy and golfbag. and the join has to be this way, it cant be the other way around.

this is what i wanna do:

delete from caddy
where caddy.pnr="141414-1414" and caddy.name="Jeppe";

but I just get the error code 1451. what can the problem be?

score 0 · Accepted Answer · answered Mar 10 '19 at 16:37

0

From the error, there is a foreign key constraint issue, that means that there is one or more rows in table golfbag that contains the primary key of the row to be deleted from table caddy as its foreign key.

Although there is a way to force delete but this is not advised as this will cause orphan records in table golfbag.

You can temporarily disable foreign key check before the delete query and enable it back after the delete is executed.

To disable foreign key check, use

SET FOREIGN_KEY_CHECKS = 0;

and to enable it, use

SET FOREIGN_KEY_CHECKS = 1;

answered Mar 10 '19 at 16:37

OPMat

463
4
14

thank you very much! it worked :D – kiwi Mar 10 '19 at 16:44
If this is as bad as you suggest it is, then why do you even provide this as an answer? – Shadow Mar 10 '19 at 16:55
The answer is provided as an answer to his question and for his education. There are instances where you really need to get things like this done. That is why I added the warning in **Bold** – OPMat Mar 10 '19 at 18:50
It is a valid answer, since there's a big warning advice. I have a situation where I really need to disable the foreign key constraint before running a migration, in a test environment. And this answers my question about how to do it. – Kar.ma Jan 04 '23 at 16:57

score 0 · Answer 2 · answered Mar 10 '19 at 16:38

You have to define the action to do on the deletion of the parent table. In your case, you may want to delete, and define it this way:

create table golfbag( 
brand varchar(20),
type varchar(20),
spelarePnr char(13),
caddyPnr char(13),
primary key(brand, spelarePnr),
foreign key(spelarePnr) references spelare(Pnr) on delete cascade,
foreign key(caddyPnr) references caddy(Pnr) on delete cascade
)engine innodb;

MYSQL error code 1451: cannot delete or update parent row: a foreign key contraint fails

2 Answers2