Prev: Database backup failure
Next: Updating Intellisense in Management Studio query editor
From: nzrdb6 on 24 Mar 2010 12:32
I'm trying to add a FK constraint to a table and get error 1785
"
Introducing FOREIGN KEY constraint 'fk_blah'
on table my_table' may cause cycles or multiple cascade paths. Specify
ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
KEY constraints.
"
I've tried put the alter table statement in a tran and roll it back if
@@error=1785 but I still get the error. Not sure if there is any way
to handle this. If there was it would be good because I've actually
got 100 similar alter table statements to be run in and my list stops
at the first occurence of error 1785.

Any ideas?
From: Harlan Messinger on 24 Mar 2010 13:58
nzrdb6 wrote:
> I'm trying to add a FK constraint to a table and get error 1785
> "
> Introducing FOREIGN KEY constraint 'fk_blah'
> on table my_table' may cause cycles or multiple cascade paths. Specify
> ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
> KEY constraints.
> "
> I've tried put the alter table statement in a tran and roll it back if
> @@error=1785 but I still get the error. Not sure if there is any way
> to handle this. If there was it would be good because I've actually
> got 100 similar alter table statements to be run in and my list stops
> at the first occurence of error 1785.

You are going to have to make sure your foreign key relationships have
no cycles or multiple cascade paths or that you don't cascade deletions
or updates. To illustrate what is meant by "cycles", suppose you have

CREATE TABLE A
(
aID int NOT NULL PRIMARY KEY,
bID int NOT NULL
)

CREATE TABLE B
(
bID int NOT NULL PRIMARY KEY,
aID int NOT NULL
)

ALTER TABLE A
ADD CONSTRAINT FK_A_B FOREIGN KEY (aID) REFERENCES B (aID)
ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE B
ADD CONSTRAINT FK_B_A FOREIGN KEY (bID) REFERENCES A (bID)
ON DELETE CASCADE ON UPDATE CASCADE

INSERT INTO A (aID, bID) VALUES (1, 5)
INSERT INTO B (bID, aID) VALUES (5, 1)

Then what do you suppose happens if you execute

DELETE A WHERE aID = 1

? Deleting the row in A forces the row in B to be deleted first--but the
row in B can't be deleted till the same row in A is deleted.

I don't know how to describe just how it causes a problem, but multiple
cascade paths also need to be avoid. These are cases where table B has a
foreign key into table A, and table C has a foreign key into both A and
B. An update to the primary key in A or the deletion of a row in A then
triggers updates to or deletion of row in C that are related to rows in
A *and* rows in C that are related to rows in B that are related to rows
in A.

So you have to make sure that your foreign key constraints that allow
cascades don't create any cycles or multiple paths of execution like
this. If you create a relationship diagram, it should have no closed loops.
 | 
Pages: 1
Prev: Database backup failure
Next: Updating Intellisense in Management Studio query editor