So, I am getting:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (
playground
.Person
, CONSTRAINTsk_Person_Parent
FOREIGN KEY (parent_id
) REFERENCESPerson
(id
) ON DELETE CASCADE ON UPDATE CASCADE)
This is the simple table, referencing itself:
CREATE TABLE IF NOT EXISTS Person (
id int not null primary key,
name varchar(100) not null,
parent_id int null,
CONSTRAINT `sk_Person_Parent`
FOREIGN KEY (parent_id)
REFERENCES Person (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
as you see, there is "ON UPDATE CASCADE". I insert 4 simple rows in it:
INSERT INTO Person(id, name, parent_id)
VALUES
(1, 'vasko', NULL),
(2, 'asdas', 1),
(3, 'ivo', 1),
(4, 'anton', 3);
so I have 1 - vasko 2 - asdasd 3 - ivo 4 - anton. When i delete by id 1, all the records get wiped, because of the ON DELETE CASCADE. However, if I try to execute
UPDATE Person
SET id=10
WHERE id=1;
I get the given error. Any ideas?
(I am expecting vasil's id to become 10, and the parent_id of the next 2 rows to be updated to 10)
See Question&Answers more detail:os