.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
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...
 
 




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification