SQL Server Performance Forum – Threads Archive

Precedence of CASCADE and NO ACTION?

Hi, This question came across my mind while I was trying to understanding this error Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint ‘fk_two’ on table ‘table2’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors. I understood what the above error meant eventually. What struck and surprised me was when I define two foreign keys that reference to same column in the same parent table, MSSQL allowed that but one with ON UPDATE/ON DELETE CASCADE and the other one with ON UPDATE/ON DELETE NO ACTION respectively. So, what’s the precedence of the ON UPDATE and ON DELETE action? NO ACTION or CASCADE?
Here is the example I used create table table1 (user_ID integer not null primary key, user_name
char(50) not null)
go create table table2 (author_ID integer not null primary key, author_name
char(50) not null, lastModifiedBy integer not null, addedby integer not
null)
go alter table table2 add constraint fk_one foreign key (lastModifiedby)
references table1 (user_ID) on delete cascade on update cascade
go –this fails with the error because it provides a second cascading path to table2.
–alter table table2 add constraint fk_two foreign key (addedby)
–references table1(user_ID) on delete no action on update cascade
–go alter table table2 add constraint fk_two foreign key (lastModifiedby)
references table1 (user_ID) on delete no action on update no action
go

Could there be a third table involved, that has FKs to the other two tables? You would get the same "multiple cascade paths" message, even if the FKs refer to different columns – which I think is a design flaw.

Hi , <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by retroman Hi, This question came across my mind while I was trying to understanding this error Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint ‘fk_two’ on table ‘table2′ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors. I understood what the above error meant eventually. What struck and surprised me was when I define two foreign keys that reference to same column in the same parent table, MSSQL allowed that but one with ON UPDATE/ON DELETE CASCADE and the other one with ON UPDATE/ON DELETE NO ACTION respectively. So, what’s the precedence of the ON UPDATE and ON DELETE action? NO ACTION or CASCADE? Here is the example I used create table table1 (user_ID integer not null primary key, user_name char(50) not null) go create table table2 (author_ID integer not null primary key, author_name char(50) not null, lastModifiedBy integer not null, addedby integer not null) go alter table table2 add constraint fk_one foreign key (lastModifiedby) references table1 (user_ID) on delete cascade on update cascade go –this fails with the error because it provides a second cascading path to table2. –alter table table2 add constraint fk_two foreign key (addedby) –references table1(user_ID) on delete no action on update cascade –go alter table table2 add constraint fk_two foreign key (lastModifiedby) references table1 (user_ID) on delete no action on update no action go <hr height="1" noshade id="quote"></blockquote id="quote"> I have check your given statements on my machine its working and after that i run sp_helpconstraint table2 it shows me all the four reference added : FOREIGN KEYfk_fourNo ActionNo ActionEnabledIs_For_ReplicationlastModifiedBy REFERENCES master.dbo.table1 (user_ID) FOREIGN KEYfk_oneCascadeCascadeEnabledIs_For_ReplicationlastModifiedBy REFERENCES master.dbo.table1 (user_ID) FOREIGN KEYfk_threeNo ActionNo ActionEnabledIs_For_ReplicationlastModifiedBy REFERENCES master.dbo.table1 (user_ID) FOREIGN KEYfk_twoNo ActionNo ActionEnabledIs_For_ReplicationlastModifiedBy REFERENCES master.dbo.table1 (user_ID) PRIMARY KEY (clustered)PK__table2__1C0818FF(n/a)(n/a)(n/a)(n/a)author_ID so please would you check it one more time . [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards. hsGoswami [email protected]

]]>