Delete On Cascade Recursive Table - SQL Server

Asked Dec 14 '18 at 16:39

Active Dec 15 '18 at 08:49

Viewed 33 times

The linked duplicate above didn't solve the problem. They only talked on possibilities that they have to resolve.

I need to delete an item on cascade on same table, so I have:

CREATE TABLE Person
(
    id int primary key identity,
    name varchar(30) not null,
    idFather int

    CONSTRAINT fkIdFather FOREIGN KEY (idFather) REFERENCES Person(id) ON DELETE CASCADE
)

I have this like result of a select:

So, when I delete Jon: Robert, Alex and Jeff need to be deleted too.

When I delete Alice: Jon, Robert, Alex and Jeff need to be deleted too.

When I did this, i got this error:

Introducing FOREIGN KEY constraint 'fkIdFather' on table 'Person' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint. See previous errors.

How can I resolve this?

edited Jun 20 '20 at 09:12

Community

asked Dec 14 '18 at 16:39

LeoHenrique

when a row in the table you reference to gets deleted, then the cascade will delete the rows here also. So deleting john would also delete Alice. Unless I am mistaken – GuidoG Dec 14 '18 at 16:55
but i dont have a fk attached to Alice... – LeoHenrique Dec 14 '18 at 17:11
yes you do. You have a foreign key on the column `idfather` on table `Person` – GuidoG Dec 17 '18 at 07:42
But yes I made a mistake, when you delete `Alice` then `John` would also be deleted – GuidoG Dec 17 '18 at 07:46
i solved this with C#, i created a `for` inside `while` – LeoHenrique Dec 17 '18 at 19:46

Delete On Cascade Recursive Table - SQL Server

0 Answers0