sql - MySql "cannot update parent row" when I have "ON UPDATE CASCADE"

Question

So, I am getting:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (playground.Person, CONSTRAINT sk_Person_Parent FOREIGN KEY (parent_id) REFERENCES Person (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)

Answer 1

It is limitation in Mysql:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations.

reference here