smartipants2002

[smartipants2002]

Hi everybody,

I am using a sql server2000.when i gave the following command :

Alter Table post
Add CONSTRAINT fk_response1 FOREIGN KEY(response)
REFERENCES post(postid)
ON DELETE CASCADE

And got the following error in return :

Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_responseto1' on table 'postIt' 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.

Any ideas anyone????????

 

[tlbroadbent]

Validate all of the cascading delete constraints in the database. Make sure the cascading deletes are not recursive in nature and that there are not multiple references to the same table(s) in the cascading delete path.

The message says that cascading delete could cause cycles. This could happen if a delete on tableA causes a delete on tableB which causes a deleted on tableC which could in turn cause another delete on tableA.

Or the contraint would create multiple delete paths which SQL Seever cannot properly resolve perhaps because one or more tables are refernced multiple times. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

Hi SP2002,
That's an interesting train of thought you are following. If you could get it to work, that would be dynamite. I had actually considered it early in our discussions, but hadn't mentioned because I didn't think it would work (although admittedly I did not actually try it.)

Unfortunately, as the error msg seems to say, and tbroadbent also picked up on, the Cascade doesn't seem to like a recursive reference to itself. Here's the relevant passage from BOL:

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE.​

Like I said, it would be great if you got the Cascade working, but I'm afraid it doesn't sound too promising.

I know we've covered it before, but I'm still stuck on the idea of another column that contains the 'fatherid' for the whole thread. I can't seem to come up with anything else.

 

[karluk]

Unfortunately, the recursive delete cascade is in the foreign key that you're trying to create. The parent and child tables are the same, so you already have recursion.

Some databases allow this type of recursion, but it looks as if SQL Server 2000 isn't to that point yet. I tried doing the same thing and got the same error. It looks as if you'll have to handle this some other way.