SQL Server 2000 - on cascade delete question.

[actionbasti]

Hello SQLers,

Working Code:

create table Rules(
	RuleID int identity(1,1) not null,
	UserID int not null,
	AccountIDSource int,
	AccountIDTarget int,
	ActionID int, 
	ActionValue money,
	NextActionDate datetime, 
	Foreign Key(UserID) References Users(UserID) on delete cascade,
	Foreign Key(AccountIDSource) References Accounts(AccountID),
	Foreign Key(AccountIDTarget) References Accounts(AccountID),
	Primary Key(RuleID)
);

NOT WORKING CODE:

create table Rules(
	RuleID int identity(1,1) not null,
	UserID int not null,
	AccountIDSource int,
	AccountIDTarget int,
	ActionID int, 
	ActionValue money,
	NextActionDate datetime, 
	Foreign Key(UserID) References Users(UserID) on delete cascade,
	Foreign Key(AccountIDSource) References Accounts(AccountID) [b]on delete cascade[/b],
	Foreign Key(AccountIDTarget) References Accounts(AccountID) [b]on delete cascade[/b],
	Primary Key(RuleID)
);

I get the follwoing error:


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

How can I accomplish what Im trying to show above:
There is an accounts table with unique account ids that are referenced twice in the rules table. so if i delete an account, i want the each rule's row to be deleted as well whether the reference is in the source, the target, OR BOTH.

Thanks

Seb

 

[r937]

i think you can do this only with a trigger

the "cycle" part of the error message is what's wrong

you'll get a better answer, i'm sure, in the microsoft sql server forum, forum183

r937.com | rudy.ca

 

[actionbasti]

Thank you..

I will check out that forum.

Seb

 

[unclejimbob]

This thread is probably a reasonable reference as to possible options.

http://www.tek-tips.com/viewthread.cfm?qid=1183422

good luck

ujb