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
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.
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.