02-21-03, 11:41 #1Registered User
- Join Date
- Aug 2002
Unanswered: cycles or multiple cascade paths FOREIGN KEY constraints
I have two tables partner and purchase_order
Purchase order have two fields customer and agent which are referring to foreign table partner. Both customer and agent are kept in same table since they have lot of common attributes
create table partner
(partner_id integer primary key,
create table purchase_order
(Po_no integer primary key,
Foreign key (Customer) references partner on delete cascade,
Foreign key (Agent) references partner on delete cascade )
Ignore SQL syntax ( above syntax works for oracle)
While creating purchase_order table , it gives errors
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_123' on table 'purchase_order ' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint
How can I achieve this ?
One way is to write a trigger (Instead of delete) on purchase_order , But that according to me is not an elegent solution
Other way to create separate tables for customer and agent which also should be avoided. Because , then I have to create multiple sets of same table with same table structure
FOR INFORMATION , ORACLE ALLOWS CREATION OF THE TABLES SMOOTHLY and WORKS PERFECTLY
Is there a way to get past the problem, without redesigning the database in SQL Server 2000 ?
Last edited by asitsaha; 02-21-03 at 11:43.
02-21-03, 13:15 #2Registered User
- Join Date
- May 2002
Re: cycles or multiple cascade paths FOREIGN KEY constraints
Unfortunately you have to either splitting up the customer/agent or using insteadof trigger in sqlserver.