|
|
Posted by Jeff North on 10/02/21 11:49
On Sat, 3 Jun 2006 17:34:14 +0000 (UTC), in
comp.databases.ms-sqlserver Erland Sommarskog <esquel@sommarskog.se>
<Xns97D7C71728295Yazorman@127.0.0.1> wrote:
>| Jeff North (jnorthau@yahoo.com.au) writes:
>| > When I execute the above I get the following error message.
>| >
>| > Msg 1785, Level 16, State 0, Line 1
>| > Introducing FOREIGN KEY constraint 'fkUserID_ch' on table
>| > 'competency_hdr' 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 1
>| > Could not create constraint. See previous errors.
>| >
>| > Now, if i swap the fields around then the error message changes to
>| > that of the fkCID field.
>| >
>| > Basically what I want is:
>| > when I delete a competency record I need all references to this record
>| > to be deleted.
>| > when I delete a user I want to set the foreign key to zero (the record
>| > must remain on the database).
>| >
>| > Obviously there is something I'm missing here. Any advice, anyone?
Thanks for your response, much appreciated.
>| SQL Server is extremly conservative with cascading foreign keys. If there
>| is the slightest suspecion there could be trouble, it won't permit you
>| define the cascade path.
Thank goodness it's the app. I thought I was misunderstanding
something.
>| I can't really say what might be the problem
>| in this case, but I guess that if you add triggers into the mix, there
>| can be intersting results.
No triggers at present but it looks as though triggers might be the
only option.
>| One way to handle cascading updates and deletes is to use triggers. The
>| drawback with this is that you cannot have any declarative referential
>| integrity at all, as constraints are checked before triggers fire.
>|
>| A better approach is probably to use a stored procedure that performs
>| the cascade from bottom up. That is, it first deletes the children and
>| then the children.
More maintenance woes :-(
>| By the way, having 0 as a dummy user for "no one at all", is probably
>| not the best design. I would rather use NULL for this.
Agreed but there are always exceptions to the rule :-)
---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Navigation:
[Reply to this message]
|