Cascade operations are very picky, you can't even cascade twice from the same table to another table:
create table A
(
AId int PRIMARY KEY
)
CREATE TABLE B
(
BId int PRIMARY KEY,
AId int FOREIGN KEY References A (AId) ON UPDATE CASCADE,
AnotherAId int FOREIGN KEY References A (AId) ON UPDATE CASCADE
)
Msg 1785, Level 16, State 0, Line 5
Introducing FOREIGN KEY constraint 'FK__B__AnotherAId__173876EA' on table 'B' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint. See previous errors.
In your case, it is
drop table c,b,a
go
create table A
(
AId int PRIMARY KEY
)
CREATE TABLE B
(
BId int PRIMARY KEY,
AId int FOREIGN KEY References A (AId) ON UPDATE CASCADE
)
CREATE TABLE C
(
CId int PRIMARY KEY
,BId int FOREIGN KEY References B (BId) ON UPDATE CASCADE
,AId int FOREIGN KEY References A (AId) ON UPDATE CASCADE
)
From
http://msdn.microsoft.com/en-us/library/ms186973(SQL.105).aspx (I can't find it stated in 2012 docs, and I looked :) "the tree of cascading referential actions must not have more than one path to any specified table. " You would have to remove any
one of the update cascade settings to make it work. The way I interpret this is because an update to A would cascade to B, and that would cascade to C through B, and to C from A.
In these cases, you generally need to just change to use a trigger and carefully control how the cascade behaves...
Louis
Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.
