0

Consider this self reference table:

create table _test_cascade
(id int not null identity primary key,
 other_id int null)

Now, lets try to create a self referencing foreign key:

alter table _test_cascade
add constraint _test_cascade_constraint
foreign key (other_id) references _test_cascade(id)
on delete set null

SQL server (11.0.2100) will give you the infamous error:

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

I know from this question that SQL Server is not very good at detecting real cycles and cascade paths, but i cannot understand how is this scenario considered a possible source for such problems, as there is no cascade action and only one foreign key.

This other question is similar, but in that case the self referencing foreign key specifies on delete cascade, which i do understand why is dangerous and should not be permited.

My question is, why is this relationship not permitted?

PD: I know that usually a self referencing table is a db-design "smell", but fixing that design is not an option for me.

Edit:

This has been marked as a duplicate of this other question, but that one is more general and the answers provided do not apply to this scenario, as in this case there is no cascade delete or multiple relantionships specified. I understand the general problem, which i dont understand is why does it applies to this case.

Daniel García Rubio
  • 432
  • 6
  • 16
  • Can't you make some kind of trigger on delete? Or set it to an dummy id? – Nebulosar Oct 25 '17 at 12:45
  • Yeah, I understand that this problem could be fixed with a trigger, but we are trying to keep it simple and not use triggers in this db – Daniel García Rubio Oct 25 '17 at 12:47
  • Also https://stackoverflow.com/q/5018099/27535 and https://stackoverflow.com/q/8287023/27535 and others – gbn Oct 25 '17 at 12:51
  • I dont agree that this is a duplicate. Those questions appointed are similar but different scenarios. This is one specific scenario for which I dont understand the restriction. – Daniel García Rubio Oct 25 '17 at 12:55
  • 1
    It does not matter if self-reference or not, the same situation applies. You can cascade update or delete a row, that is already being updated by the parent update = conflict. `SET IDENTITY_INSERT _test_cascade ON; UPDATE _test_cascade SET id = id+1, other_id = other_id -1;` – gbn Oct 25 '17 at 13:08
  • Thanks for the help, but I still fail to see the problem. In this case there is no cascade delete, if the parent gets deleted, all the children get their FKs set to null, and the path ends. There are no multiple paths or cycles, as I see it. – Daniel García Rubio Oct 25 '17 at 13:45

0 Answers0