Re: Cascading Changes in SQL Server

From: Ryan (ryan.d.rembaum_at_kp.org)
Date: 08/30/04 

Date: 30 Aug 2004 14:50:58 -0700

Hi Dejan,

Thanks. Just to clarify, is this article saying that SQL server
determines whether a key will might cause cycles or multiple cascade
paths based simply on the table in its entirety, as opposed to whether
the actual fields you will be updating could possibly cause such an
event?

In the example given, I can understand the problem, in that the setup
has two cascading updates to the same field on the many side of the
relationship. This is not the case in my example. There are not
multiple paths at the field level. A change to a state ID (in
TABLESTATE) should cause an update in the three tables it is related
to. Those tables have relationships with each other that do not
include the table TABLESTATE.

Again, though, from the TABLE level I could see how SQL might flag it.
 Am I correct in what I am getting from this?

If so, this seems too bad since I think it would be easier to manage
if I could save triggers for things relationships can't do. Strange
that Access can establish the necessary relationships and SQL can't.
Oh well!

Thanks!

"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in message news:<uak74uZjEHA.704@TK2MSFTNGP09.phx.gbl>...
> Check the article at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;321843, it is about
> this error.
>
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
> "Ryan" <ryan.d.rembaum@kp.org> wrote in message
> news:b5cda00e.0408271644.1466e73f@posting.google.com...
> > I am converting a database from Access to SQL Server and am trying to
> > set up a relationship like the one that had been set in Access. This
> > relationship had cascading deletes and updates. When I try to set up
> > the relationship in SQL server, I get the message (as an example):
> >
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> > FOREIGN KEY constraint 'FK_States-StateID' on table
> > 'PersonnelInformation' 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 don't really understand why I am getting this message, unless
> > cascading updates are bidirectional (meaning that a change to the
> > foreign key would cascade over to the primary key:
> >
> > I basically have the following setup (I am using an example that is a
> > bit easier to understand than my actual table setup:
> >
> > TABLECOMPANY TABLECEO TABLESTATE TABLESubsidiary
> > CompanyTaxID CompanyTaxID StateID CompanyTaxID
> > Company CEOName STATE SubsidiaryTaxID
> > StateID AddressLine1 StateID
> > ...
> > StateID
> >
> > So basically I set a primary key to foreign key relationship from
> > TABLECOMPANY (PK) To TABLECEO (FK) on CompanyTaxID (Cascade Deletes
> > and Updates).
> >
> > I also set a primary to foreign Key relationship from TABLECOMPANY
> > (PK) to TABLESUBSIDIARY (FK), again on CompanyTaxID (Cascade Deletes
> > and Updates).
> >
> > Then I create a primary to foreign key relationship from TABLESTATE
> > (PK) to Each of the other three tables on StateID (Update only).
> >
> > This configuration fails. I don't understand why, though clearly the
> > relationships with TABLESTATE are the problem. But since TABLE state
> > contains no foreign keys, nor does it specify any cascading deletes,
> > no changes made anywhere could create a loop. Unless a change in a
> > foreign key could affect the primary key, which doesn't make
> > sense...??
> >
> > Does anyone know why this is happening. Is there a solution, or do I
> > HAVE to use triggers?
> >
> > Thanks,
> > Ryan

Relevant Pages

  • Re: How to delete related records using CASCADE on delete option
    ... One of SQL Server 2000's limitations is that cascading deletes and updates ... Enforce referential integrity through a foreign key without cascading ...
    (microsoft.public.sqlserver.programming)
  • Re: [NOW ANSWER Aarons QUESTION CELKO]
    ... the single unique value is stored in the ... Great when you have a foreign key with cascading updates, ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.programming)
  • Re: multiple cascade paths
    ... Obviously, with commit-time validation of constraints, we could get ... But this is a newsgroup on SQL Server, and all data-modelling I do is ... I'd prefer to see all the mutual foreign key ... using a stored proc or trigger to do the cascading. ...
    (comp.databases.ms-sqlserver)
  • Re: Cannot update
    ... not install the updates you referred to. ... highly recommend way for the later one is to install SQL server, ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: multiple cascade paths
    ... I'll address some and snip the rest of the post. ... The only reason to go to SQL Server now is because right ... cascading deletes in some other way. ... most foreign key constraints don't form cycles or multiple cascade ...
    (comp.databases.ms-sqlserver)
Loading