2

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)

Gouda Elalfy
  • 6,888
  • 1
  • 26
  • 38
vasil todorov
  • 145
  • 1
  • 11
  • It is an interesting foreign key approach. The problem is: when you try to change the id, which is not usual actually but let's say that's fine, it **first** attempts to change the foreign key - since id=10 doesn't exist yet, it fails. Simply I don't see any workaround to solve it easily and curious to see if any (other than using temporary table approach perhaps). – smozgur Feb 07 '16 at 15:41
  • well I was reading a db book and there was a topic about hierarchies, so I wanted to experiment and then this came up to my mind. No an issue which is critical for my work on a project, just wondering. – vasil todorov Feb 07 '16 at 15:42
  • ON UPDATE CASCADE looks to be useless in this structure. I would have to manage it manually for the effected records. As I said, I am really curious to see if there is a workaround. – smozgur Feb 07 '16 at 15:44
  • well I am curious in what case ON UPDATE CASCADE is useful anyway? I mean the FK always points to a PK, so if this does not work, where does it come to use? – vasil todorov Feb 07 '16 at 15:47
  • never mind, I answered my question - if the FK is from another table, it works OK. Looks like you need to use some kind of triggers if you want to update a self referencing table. – vasil todorov Feb 07 '16 at 15:49

1 Answers1

4

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

Barmar
  • 741,623
  • 53
  • 500
  • 612
Gouda Elalfy
  • 6,888
  • 1
  • 26
  • 38