I am trying to write a case statement that will look at a relationship between two fields. I have done this in the past but not one that needs to look at various options in one of the fields.
Example. I am comparing a location code to a code where an employee's check is mailed.
CASE
WHEN EjLocation ='AGACES'
THEN '3410'
But I need to also include 3415 and any check code that begins with 83 (i.e. 8315, 8350). How do I include this in the THEN statement?
Thanks!Could you post a small sample set and the result set you would like to see?|||Originally posted by Teddy
Could you post a small sample set and the result set you would like to see?
Here is the query that I have started. Just need to work on the CASE statement.
My goal is to just show employees whose EjLocation doesn't match the PrbCheckSeqNo listed in the THEN statement.
IE
Name Location CheckCode
John Smith AGACES 8500
This person would show up because they don't equal 3410, 3415 or a code starting with 83##.
SELECT EpLastName, EpFirstName, EjLocation, PrbCheckSeqNo
FROM EJob, EBase, EPayrollBase
/* Check codes not matching facility code- excludes virtuals
Dev. T.Rucker 1/04 */
WHERE EpFlxideb = EjFlxideb AND Ebflxid = Prbflxideb AND PrbDateend is null AND EjDateEnd IS NULL AND Epflxideb = EeFlxideb AND
EeDateEnd IS NULL AND NOT PrbCheckSeqNo = '0000' AND
CASE
WHEN EjLocation ='AGACES'
THEN '3410'
WHEN EjLocation = 'BALCES'
THEN '2300'
ELSE 'O'
END <> PrbCheckSeqNo
ORDER BY EjLocation, PrbCheckSeqNo
Showing posts with label relationship. Show all posts
Showing posts with label relationship. Show all posts
Tuesday, March 27, 2012
Monday, March 19, 2012
Cascading Referential Integrity Constraints
In a master-detail one-to-many relationship, I have the foreign key set to '
allow null'. I would like, in this particular case, to automatically have th
e foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will al
ways delete the detail/many records when the master/one record is deleted. I
f the foreign key is nullable, would it not make sense to null it, and if it
is not nullable to delete
the detail/many records?
Is there any efficient way to set a table up so that foreign keys are automa
tically nulled when the primary key record is deleted?That functionality won't be available until the next release of SQL Server
(Yukon). Meanwhile, you will have to handle RI through triggers in that
case. This link may be useful:
http://msdn.microsoft.com/library/d...efintegrity.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:025F9D33-F01D-43AA-BF12-09C50AAD4670@.microsoft.com...
In a master-detail one-to-many relationship, I have the foreign key set to
'allow null'. I would like, in this particular case, to automatically have
the foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will
always delete the detail/many records when the master/one record is deleted.
If the foreign key is nullable, would it not make sense to null it, and if
it is not nullable to delete the detail/many records?
Is there any efficient way to set a table up so that foreign keys are
automatically nulled when the primary key record is deleted?
allow null'. I would like, in this particular case, to automatically have th
e foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will al
ways delete the detail/many records when the master/one record is deleted. I
f the foreign key is nullable, would it not make sense to null it, and if it
is not nullable to delete
the detail/many records?
Is there any efficient way to set a table up so that foreign keys are automa
tically nulled when the primary key record is deleted?That functionality won't be available until the next release of SQL Server
(Yukon). Meanwhile, you will have to handle RI through triggers in that
case. This link may be useful:
http://msdn.microsoft.com/library/d...efintegrity.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:025F9D33-F01D-43AA-BF12-09C50AAD4670@.microsoft.com...
In a master-detail one-to-many relationship, I have the foreign key set to
'allow null'. I would like, in this particular case, to automatically have
the foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will
always delete the detail/many records when the master/one record is deleted.
If the foreign key is nullable, would it not make sense to null it, and if
it is not nullable to delete the detail/many records?
Is there any efficient way to set a table up so that foreign keys are
automatically nulled when the primary key record is deleted?
Sunday, March 11, 2012
Cascading Deletes
When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?
SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?
Has anyone scanned the landscape for a merry-go-round?
:D
Wholly disconnected ramblings bart man...
Seriously...be careful with cascading...should be no need...
I never liked messing with keys...
damn surrogates...
To me, if a key changes, then it's a new entity...or the key is defined improperly...
You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.
Disconneted ramblings...
...many...non-sequiturs...
Wish I had a key for elipsis so I didn't have to hit the period key three times...
Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...
pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...
Cool...
You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication
maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?
WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE
Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.
Again, do I need to write triggers to accomplish my goal here :confused:
I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:
and you dont have to create database devices anymore.. :D
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?
SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?
Has anyone scanned the landscape for a merry-go-round?
:D
Wholly disconnected ramblings bart man...
Seriously...be careful with cascading...should be no need...
I never liked messing with keys...
damn surrogates...
To me, if a key changes, then it's a new entity...or the key is defined improperly...
You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.
Disconneted ramblings...
...many...non-sequiturs...
Wish I had a key for elipsis so I didn't have to hit the period key three times...
Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...
pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...
Cool...
You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication
maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?
WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE
Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.
Again, do I need to write triggers to accomplish my goal here :confused:
I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:
and you dont have to create database devices anymore.. :D
Cascading delete
I have one master tables with 4 relational tables
The relationship between the tables is not cascading delete.
But I like to write one query that will delete record from the master table
as well as relational tables.
Let me know how can i do this.
Thanks in advanceThis newsgroup is for reporting services. Try one of the other SQL Server
newsgroups.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23%2355VDOtEHA.220@.TK2MSFTNGP15.phx.gbl...
> I have one master tables with 4 relational tables
> The relationship between the tables is not cascading delete.
> But I like to write one query that will delete record from the master
table
> as well as relational tables.
> Let me know how can i do this.
> Thanks in advance
>
The relationship between the tables is not cascading delete.
But I like to write one query that will delete record from the master table
as well as relational tables.
Let me know how can i do this.
Thanks in advanceThis newsgroup is for reporting services. Try one of the other SQL Server
newsgroups.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23%2355VDOtEHA.220@.TK2MSFTNGP15.phx.gbl...
> I have one master tables with 4 relational tables
> The relationship between the tables is not cascading delete.
> But I like to write one query that will delete record from the master
table
> as well as relational tables.
> Let me know how can i do this.
> Thanks in advance
>
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,
Ryan
Check the article at
http://support.microsoft.com/default...;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.c om...
> 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>...[vbcol=seagreen]
> Check the article at
> http://support.microsoft.com/default...;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.c om...
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
Check the article at
http://support.microsoft.com/default...;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.c om...
> 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>...[vbcol=seagreen]
> Check the article at
> http://support.microsoft.com/default...;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.c om...
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
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
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]Intro
ducing
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/defaul...b;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]Int
roducing
> 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 creat
e
> 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:<uak
74uZjEHA.704@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Check the article at
> http://support.microsoft.com/defaul...b;en-us;321843, it is abou
t
> 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...
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]Intro
ducing
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/defaul...b;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]Int
roducing
> 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 creat
e
> 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:<uak
74uZjEHA.704@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Check the article at
> http://support.microsoft.com/defaul...b;en-us;321843, it is abou
t
> 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...
Thursday, March 8, 2012
Cascade delete relationship slower?
I have several related tables that have the cascade delete option selected i
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:
> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.
> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:
> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.
> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>
Cascade Delete Error on FK
I'm getting the following error when I try to set the Delete action for a
relationship to Cascade. What I have is 4 tables. The base table, a child1
table, a child2 table, and a child1_mm_child2 table. For each Base object
(row) can have multiple child1 rows and multiple child2 rows. For a Base
object, for each child1 and child2 row there is a child1_mm_child2 row (in
other words an object with 2 child1 rows and 3 child2 rows would have 6
(2*3) child1_mm_child2 rows). What I want to do is when a Base object is
deleted, all related rows in all other 3 tables are deleted, hence the
Cascade On Delete relationships. I'm able to create all but 1 of the
relationships, the final one errors out.
"- Unable to create relationship 'FK_Sources_CIP'.
Introducing FOREIGN KEY constraint 'FK_Sources_CIP' on table 'Sources' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."
Thanks for any assistance,
RyanHi Ryan,
Per my understanding, you were trying to create the tables like the
following:
CREATE TABLE [dbo].[Base] (
[BaseID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1] (
[Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child2] (
[Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1_2] (
[Child1ID] [int] NOT NULL ,
[Child2ID] [int] NOT NULL
) ON [PRIMARY]
GO
You wanted to append the cascade delete on the following relations:
FK_BASE_CHILD1
FK_BASE_CHILD2
FK_CHILD1_CHILD1_2
FK_CHILD2_CHILD1_2
The problem was that the last relation failed to be created.
If I have misunderstood, please let me know.
The behavior is expected if your tables were designed like the above. A
logically reasonable design should be that remove the relations
FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the table
Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
For example:
Base:
BaseID BaseName
1 B1
2 B2
3 B3
Child1:
Child1ID BaseID Child1Name
1 1 C1
2 1 C2
Child2:
Child2ID BaseID Child2Name
1 1 D1
2 2 D2
Child1_2:
Child1ID Child2ID
1 1
1 2
2 1
2 2
This is original design, but it is not reasonable. In this case, when you
execute "delete from Base where BaseID=1", what is your expected result?
Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
deleted, and the row (1,1,D1) in Child2 should be deleted; however should
the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
two rows should not have been existed in the table?
Hope this helps.
If you have any other quesitons or concerns, pleae feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles,
You have it exactly right. It does look like I will need to add a FK to the
Child1_2 table for Base and apply the cascade delete relationship to that as
well. Thank you for your assistance.
Ryan
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:CJNRLDXVHHA.1580@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ryan,
> Per my understanding, you were trying to create the tables like the
> following:
> CREATE TABLE [dbo].[Base] (
> [BaseID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1] (
> [Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child2] (
> [Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1_2] (
> [Child1ID] [int] NOT NULL ,
> [Child2ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> You wanted to append the cascade delete on the following relations:
> FK_BASE_CHILD1
> FK_BASE_CHILD2
> FK_CHILD1_CHILD1_2
> FK_CHILD2_CHILD1_2
> The problem was that the last relation failed to be created.
> If I have misunderstood, please let me know.
> The behavior is expected if your tables were designed like the above. A
> logically reasonable design should be that remove the relations
> FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the
> table
> Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
> For example:
> Base:
> BaseID BaseName
> 1 B1
> 2 B2
> 3 B3
> Child1:
> Child1ID BaseID Child1Name
> 1 1 C1
> 2 1 C2
> Child2:
> Child2ID BaseID Child2Name
> 1 1 D1
> 2 2 D2
> Child1_2:
> Child1ID Child2ID
> 1 1
> 1 2
> 2 1
> 2 2
> This is original design, but it is not reasonable. In this case, when you
> execute "delete from Base where BaseID=1", what is your expected result?
> Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
> deleted, and the row (1,1,D1) in Child2 should be deleted; however should
> the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
> two rows should not have been existed in the table?
> Hope this helps.
> If you have any other quesitons or concerns, pleae feel free to let me
> know.
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||Hi, Ryan,
Thanks for your updating and response.
I am very glad to hear that the suggestions are helpful. If you encounter
any issues in future, please feel free to post here.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
relationship to Cascade. What I have is 4 tables. The base table, a child1
table, a child2 table, and a child1_mm_child2 table. For each Base object
(row) can have multiple child1 rows and multiple child2 rows. For a Base
object, for each child1 and child2 row there is a child1_mm_child2 row (in
other words an object with 2 child1 rows and 3 child2 rows would have 6
(2*3) child1_mm_child2 rows). What I want to do is when a Base object is
deleted, all related rows in all other 3 tables are deleted, hence the
Cascade On Delete relationships. I'm able to create all but 1 of the
relationships, the final one errors out.
"- Unable to create relationship 'FK_Sources_CIP'.
Introducing FOREIGN KEY constraint 'FK_Sources_CIP' on table 'Sources' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."
Thanks for any assistance,
RyanHi Ryan,
Per my understanding, you were trying to create the tables like the
following:
CREATE TABLE [dbo].[Base] (
[BaseID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1] (
[Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child2] (
[Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1_2] (
[Child1ID] [int] NOT NULL ,
[Child2ID] [int] NOT NULL
) ON [PRIMARY]
GO
You wanted to append the cascade delete on the following relations:
FK_BASE_CHILD1
FK_BASE_CHILD2
FK_CHILD1_CHILD1_2
FK_CHILD2_CHILD1_2
The problem was that the last relation failed to be created.
If I have misunderstood, please let me know.
The behavior is expected if your tables were designed like the above. A
logically reasonable design should be that remove the relations
FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the table
Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
For example:
Base:
BaseID BaseName
1 B1
2 B2
3 B3
Child1:
Child1ID BaseID Child1Name
1 1 C1
2 1 C2
Child2:
Child2ID BaseID Child2Name
1 1 D1
2 2 D2
Child1_2:
Child1ID Child2ID
1 1
1 2
2 1
2 2
This is original design, but it is not reasonable. In this case, when you
execute "delete from Base where BaseID=1", what is your expected result?
Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
deleted, and the row (1,1,D1) in Child2 should be deleted; however should
the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
two rows should not have been existed in the table?
Hope this helps.
If you have any other quesitons or concerns, pleae feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles,
You have it exactly right. It does look like I will need to add a FK to the
Child1_2 table for Base and apply the cascade delete relationship to that as
well. Thank you for your assistance.
Ryan
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:CJNRLDXVHHA.1580@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ryan,
> Per my understanding, you were trying to create the tables like the
> following:
> CREATE TABLE [dbo].[Base] (
> [BaseID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1] (
> [Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child2] (
> [Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1_2] (
> [Child1ID] [int] NOT NULL ,
> [Child2ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> You wanted to append the cascade delete on the following relations:
> FK_BASE_CHILD1
> FK_BASE_CHILD2
> FK_CHILD1_CHILD1_2
> FK_CHILD2_CHILD1_2
> The problem was that the last relation failed to be created.
> If I have misunderstood, please let me know.
> The behavior is expected if your tables were designed like the above. A
> logically reasonable design should be that remove the relations
> FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the
> table
> Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
> For example:
> Base:
> BaseID BaseName
> 1 B1
> 2 B2
> 3 B3
> Child1:
> Child1ID BaseID Child1Name
> 1 1 C1
> 2 1 C2
> Child2:
> Child2ID BaseID Child2Name
> 1 1 D1
> 2 2 D2
> Child1_2:
> Child1ID Child2ID
> 1 1
> 1 2
> 2 1
> 2 2
> This is original design, but it is not reasonable. In this case, when you
> execute "delete from Base where BaseID=1", what is your expected result?
> Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
> deleted, and the row (1,1,D1) in Child2 should be deleted; however should
> the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
> two rows should not have been existed in the table?
> Hope this helps.
> If you have any other quesitons or concerns, pleae feel free to let me
> know.
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||Hi, Ryan,
Thanks for your updating and response.
I am very glad to hear that the suggestions are helpful. If you encounter
any issues in future, please feel free to post here.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
cascade delete can not apply to two foreign keys
I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?
Table A:
ID
ProductID <foreign key>
CustomerID <foreign key
Table Product
ProductID <primary key
Table Customer
CustomerID <primary key
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.In my opinion, relying on cascading updates and deletes isn't a good thing to do anyway. I think it's better to handle the deletes in your code when you explicitly want them to happen. I.e., the procedures to delete from either the product or customer table should include a delete statement against Table A. I don't think cascading deletes are a good idea to replace your own logic.
Subscribe to:
Posts (Atom)