|
|
Posted by Erland Sommarskog on 06/03/06 17:34
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?
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. 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.
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.
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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|