Re: Multiple Cascade Paths
From: Andrew John (aj_at_DELETEmistrose.com)
Date: 07/27/04
- Next message: Andrew John: "Re: Service Agent Not starting"
- Previous message: David Portas: "Re: Fun Order By Statement.."
- In reply to: Luke Amery: "Multiple Cascade Paths"
- Next in thread: Luke Amery: "Re: Multiple Cascade Paths"
- Reply: Luke Amery: "Re: Multiple Cascade Paths"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 27 Jul 2004 17:22:21 +1000
Luke,
Books Online does say why:
"No table can appear more than once in the list of all cascading
referential actions that result from the DELETE or UPDATE. The tree of
cascading referential actions must not have more than one path to any
given table"
See index under "Cascading Referential Integrity Constraints"
In your example an update of a can cause an update on c either directly,
or via table b, so that explains the error.
The only work around I know of, is to replace ONE OF the FK paths with
triggers. Not neat, but it is workable. Before you get too down on MS
for this have you tried Oracle or MySQL ? "mutating tables error" when
you even try to look at the base table in a trigger, or no triggers at
all ( MySQL version < 5.1)
Regards
AJ
>Luke Amery wrote:
> Hi all,
>
> I have an issue with foreign key constraints and multiple cascade paths as
> the title of my post would suggest.
>
> Here is a simple example:
>
> CREATE TABLE a
> (
> keyofa int not null primary key
> )
>
> CREATE TABLE b
> (
> keyofb int not null primary key,
> fkinatob int not null foreign key references a(keyofa) on update cascade
> )
>
> CREATE TABLE c
> (
> keyofc int not null primary key,
> fkinctob int not null foreign key references b(keyofb) on update
> cascade,
> fkinctoa int not null foreign key references a(keyofa) on update cascade
> )
>
>
> if you run it you will see that the foreign key linking c to a is invalid
> due to either a cycle or multiple cascade path.
>
> It can't be a cycle - because there are no cycles in this example (that I
> can see).
>
> It must be a multiple cascade path - I haven't found a definition of this -
> but according to any definition I can dream up - there is no way to multiply
> cascade a change to c through changing a or b. So I consider this a short
> coming of SQL Server.
>
> Ok, so sql server dropped the ball - and Microsoft isn't going to fix it for
> me, so what are my alternatives?
>
> I can use triggers - I can remove the on update cascade options on the
> foreign key constraints that refer to table a - then put a trigger on table
> a to keep table b and table c in sync with table a. Trouble with this is - I
> can no longer update table a in any way because the constraints are checked
> before the trigger is fired.
>
> So my next option is to drop the constraints all together and use triggers
> on table a to keep table b and table c in sync. Then I can use triggers on
> table b and table c to enforce integrity manually - this I think will work.
> But is this what I have to do???? what a pathetic solution!!
>
> anybody got any other ideas?
>
>
- Next message: Andrew John: "Re: Service Agent Not starting"
- Previous message: David Portas: "Re: Fun Order By Statement.."
- In reply to: Luke Amery: "Multiple Cascade Paths"
- Next in thread: Luke Amery: "Re: Multiple Cascade Paths"
- Reply: Luke Amery: "Re: Multiple Cascade Paths"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
