MBR IT/.NET 247 : Cascading Deletes on ASPFriends.com 'ngfx-sqlclient' list

Cascading Deletes

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

Little, Ambrose

I'm trying to set up constraints no SQL 2000 that will cascade deletes.
Everything's fine except for the tables that are used to link two tables.
For example, I've got a Customers and Accounts table. I've got a Custodians
table that is just a many-to-many relationship linking the Customers and
Accounts. It looks like:
AccountId (PK, FK1) relates to the primary key of the Accounts table
CustomerId (PK, FK2) relates to the primary key of the Customers table

Now, I think that I should be able to set constraints on both of these
relationships so that when either an account or a customer record is
deleted, it will delete all corresponding records in the Custodian table.
I'm pretty sure I could do this with triggers, so I'm really unclear as to
why I'm getting the following error when I try to set up the cascading
delete constraint (NOTE: I've already got the cascading delete constraint
set up on the Custodian_Accounts relationship.):
- Unable to create relationship 'Customers_Custodians_FK1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'Customers_Custodians_FK1' on table 'Custodians' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.

I'd really appreciate some insight into this, and if what I'm trying to do
is impossible with constraints, I would appreciate other recommendations, as
I think what I'm trying to do makes good sense.

Thanks.

--Ambrose

******************************************************************************
The Company reserves the right to amend statements
made herein in the event of a mistake. Unless expressly
stated herein to the contrary, only agreements in writing signed
by an authorized officer of the Company may be enforced against it.
*******************************************************************************

Reply to this message...

Bill Swartz

Hi Ambrose,

I've included a little blurb from books online that I
think addresses your issue.

Without seeing all the related tables, it's hard to
specifically help.

However, I setup a little test that I think
duplications your description and it worked fine.

IE: I have Table A, Table B, and Table C (B would be
your custodian table) The PK and FK architecture I
think is identical to your description.

Table B has FKs to both table A and C with cascading
deletes turned on.

If I delete a record from table B (the linking
table). Tables A and C are left alone. If I delete
a record from either table A, or C, the linking
record in table B is deleted just fine.

So, based upon this test, I think there is another
problem in your architecture that is causing the
error message.

Bill

(Here is MS's comment on Cascading Deletes)

The series of cascading referential actions triggered
by a single DELETE or UPDATE must form a tree
containing no circular references. 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.
Any branch of the tree is terminated when it
encounters a table for which NO ACTION has been
specified or is the default.

--- Original Message ---
From: "Little, Ambrose" <Click here to reveal e-mail address>
To: "ngfx-sqlclient" <Click here to reveal e-mail address>
Subject: [ngfx-sqlclient] Cascading Deletes

>I'm trying to set up constraints no SQL 2000 that
will cascade deletes.
>Everything's fine except for the tables that are
used to link two tables.
>For example, I've got a Customers and Accounts
table. I've got a Custodians
>table that is just a many-to-many relationship
linking the Customers and
[Original message clipped]

Reply to this message...

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com