question

ikramonly avatar image
ikramonly asked

foreign key relationship, on delete cascade on update casecade

1 down vote favorite In sql server 2012 , I have table PurchaseReturn(VendorId,PurchaseOrderId). The column 'Vendorid' is a foreign key relationship to Vendor(VendorId) and 'Purchaseorderid' is to Purchaseorder(purchaseorderi). Now my target is to set type of foreign key for 'VendorId' as 'On delete Set Null on Update Cascade' but For 'purchaseorderid' it should be 'On delete CasCade On Update Cascade'. But when i have created FK for VendorId and trying to make it for PurchaseOrderId by following query: `alter table PurchaseReturn add constraint FK_PR_PORD foreign key (Purchaseorderid) references PurchaseOrder(Purchaseorderid) on delete cascade ON UPDATE CASCADE` '; an errors appears like this: `Introducing FOREIGN KEY constraint 'FK_PR_PORD' on table 'PurchaseReturn' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.` What is the problem?... plz help thanks in advance
foreign-keyrelationshipscascade
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
I'm not sure what the accepted best practice is in these circumstances, I might be on the wrong side of it. But I've always avoided the use of cascading foreign keys in preference to having explicit removal and update procedures for related record groups. For me, having dml logic in the keys is a spaghettifying action that can make it harder to understand how and why records are being affected.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 tend to agree
0 Likes 0 ·
+1 Seconded. Not as bad as triggers but close!
0 Likes 0 ·
Coding it up yourself is great when you can guarantee that only your application will every modify the data. Some customers don't have that luxury. For them, I think cascading keys definitely have a place when solving certain types of problems (I still give you +1 for raising the other side of the issue. This question is a great example of how you can get in trouble with cascading keys).
0 Likes 0 ·
KenJ avatar image
KenJ answered
On the face of it, you can certainly create these three tables with the foreign keys you have described: use tempdb create table PurchaseReturn(VendorId int, PurchaseOrderId int) create table Vendor(VendorId int not null constraint pk_vendor primary key clustered) create table PurchaseOrder(PurchaseOrderId int constraint pk_purchaseorder primary key clustered) alter table PurchaseReturn add constraint FK_PR_VEND foreign key (VendorId) references Vendor(VendorId) ON DELETE SET NULL ON UPDATE CASCADE alter table PurchaseReturn add constraint FK_PR_PORD foreign key (PurchaseOrderId) references PurchaseOrder ON DELETE CASCADE ON UPDATE CASCADE drop table PurchaseReturn drop table Vendor drop table PurchaseOrder You have some other foreign keys defined to or from tables that are outside of this immediate problem description. Track down which other foreign keys you have defined on other tables that reference Vendor and PurchaseOrder and which foreign keys on Vendor and PurchaseOrder reference other tables. Somewhere in that web the foreign keys the loop will return to one of these three tables.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.