Cascading Changes in SQL Server

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,
RyanCheck 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|||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