uninstall SQL: deletes

Showing posts with label deletes. Show all posts

Monday, March 19, 2012

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
--
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:
> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
> >I have 3 tables set up in the typical customer - order - order item setup.
> >I
> > have cascading updates & deletes on the customer and order tables. Primary
> > &
> > secondary keys are correct.
> > When I delete, or update the customer table key field, it takes about 60
> > seconds to cascade to the child tables. If I execute manually the delete
> > operation startting at the order item table, then the orders, and finally
> > the
> > customer table it only takes a second.
> > Why is the cascading taking so long, when doing the same task manually
> > takes
> > a fraction of the time?
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>> If you do the manual delete, do you have the cascading foreign leys in place
>> or not? If you don't, you can probably fix the issue by creating indexes on
>> the foreign key columns.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
>> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>> >I have 3 tables set up in the typical customer - order - order item setup.
>> >I
>> > have cascading updates & deletes on the customer and order tables. Primary
>> > &
>> > secondary keys are correct.
>> > When I delete, or update the customer table key field, it takes about 60
>> > seconds to cascade to the child tables. If I execute manually the delete
>> > operation startting at the order item table, then the orders, and finally
>> > the
>> > customer table it only takes a second.
>> > Why is the cascading taking so long, when doing the same task manually
>> > takes
>> > a fraction of the time?
>>

Cascading Updates / Delete Problem

Sunday, March 11, 2012

cascading deletes through a 1-M join table

Hi all,
I have three tables, two with unique integer PK's and a join table for a 1-M
join.
PARENT JOIN CHILD
+--+ +--+--+ +--+
+ 7 + --> + 7 + 45 + --> + 45 +
+--+ +--+--+ +--+
+ 7 + 197 + --> + 197 +
+--+--+ +--+
I want to be able to delete a PARENT record and have it clean up the JOIN
and all CHILD entries.
The join table is the Foreign key table. If I set up cascade delete from
Parent to JOIN that works, but i can't set up cascade delete from JOIN to
CHILD because the keys in the JOIN table are not primary keys.
I know that I can do this in a trigger on JOIN but this is a natural
straight parent->child delete, is there a way to cascade delete through the
JOIN to the CHILD?
thanks!
John"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23A0gjCoUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have three tables, two with unique integer PK's and a join table for a
> 1-M join.
> PARENT JOIN CHILD
> +--+ +--+--+ +--+
> + 7 + --> + 7 + 45 + --> + 45 +
> +--+ +--+--+ +--+
> + 7 + 197 + --> + 197 +
> +--+--+ +--+
> I want to be able to delete a PARENT record and have it clean up the JOIN
> and all CHILD entries.
> The join table is the Foreign key table. If I set up cascade delete from
> Parent to JOIN that works, but i can't set up cascade delete from JOIN to
> CHILD because the keys in the JOIN table are not primary keys.
> I know that I can do this in a trigger on JOIN but this is a natural
> straight parent->child delete, is there a way to cascade delete through
> the JOIN to the CHILD?
>
Create a unique index on the JOIN table to support the relationship to
CHILD.
David

Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?
I was hoping someone had experimented and found which works best.DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below. In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go. The reason is reliability related because DRI(declarative referential integrity) with Cascade Delete and Cascade Update is guaranteed.
On a side note if you have a why question run a search for Peter Chen 1976 ERD paper on Google and see the 26 pages paper in Algebra that created DRI(declarative referential integrity) in ANSI SQL. Hope this helps.|||Thanks for the great info. All my servers run SQL 2000 so we'll be going with the built-in cascading deletes.

Cascading Deletes

I have to SQL 2000 Server setups. It seems I have different builds on them.
The problem one is (told to me from SQL Analyzer) 8.00.194 the working one
is 8.00.760. In the problem one I can not setup a cascading delete from
Enterprise Manager. Anyone know why? Getting the problem updated is going
to be a bear. Anyone know a work around to add my Cascading Delete?
ChrisJust create your foreign key in T-SQL:
ALTER TABLE your_table DROP CONSTRAINT your_constraint
GO
ALTER TABLE your_table ADD CONSTRAINT your_constraint
FOREIGN KEY (referencing_column)
REFERENCES referred_table (primary_key_column)
ON CASCADE DELETE
Jacco Schalkwijk
SQL Server MVP
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote
in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>I have to SQL 2000 Server setups. It seems I have different builds on
>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>working one is 8.00.760. In the problem one I can not setup a cascading
>delete from Enterprise Manager. Anyone know why? Getting the problem
>updated is going to be a bear. Anyone know a work around to add my
>Cascading Delete?
> Chris
>|||Ya I tried that too:
ALTER TABLE Holiday ADD CONSTRAINT TestContraint
FOREIGN KEY (FK_CallFlow)
REFERENCES CallFlows (PRI_ID)
ON CASCADE DELETE
And get this error:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ON'.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Just create your foreign key in T-SQL:
> ALTER TABLE your_table DROP CONSTRAINT your_constraint
> GO
> ALTER TABLE your_table ADD CONSTRAINT your_constraint
> FOREIGN KEY (referencing_column)
> REFERENCES referred_table (primary_key_column)
> ON CASCADE DELETE
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
> wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>>I have to SQL 2000 Server setups. It seems I have different builds on
>>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>>working one is 8.00.760. In the problem one I can not setup a cascading
>>delete from Enterprise Manager. Anyone know why? Getting the problem
>>updated is going to be a bear. Anyone know a work around to add my
>>Cascading Delete?
>> Chris
>|||> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
...
on delete cascade
AMB
"Chris, Master of All Things Insignifican" wrote:
> Ya I tried that too:
> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
> And get this error:
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'ON'.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> > Just create your foreign key in T-SQL:
> >
> > ALTER TABLE your_table DROP CONSTRAINT your_constraint
> > GO
> > ALTER TABLE your_table ADD CONSTRAINT your_constraint
> > FOREIGN KEY (referencing_column)
> > REFERENCES referred_table (primary_key_column)
> > ON CASCADE DELETE
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
> > wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
> >>I have to SQL 2000 Server setups. It seems I have different builds on
> >>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
> >>working one is 8.00.760. In the problem one I can not setup a cascading
> >>delete from Enterprise Manager. Anyone know why? Getting the problem
> >>updated is going to be a bear. Anyone know a work around to add my
> >>Cascading Delete?
> >>
> >> Chris
> >>
> >
> >
>
>|||Nope, same error message? Anyone know what's going on?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
> ...
> on delete cascade
>
> AMB
> "Chris, Master of All Things Insignifican" wrote:
>> Ya I tried that too:
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
>> And get this error:
>> Server: Msg 156, Level 15, State 1, Line 4
>> Incorrect syntax near the keyword 'ON'.
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote
>> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Just create your foreign key in T-SQL:
>> >
>> > ALTER TABLE your_table DROP CONSTRAINT your_constraint
>> > GO
>> > ALTER TABLE your_table ADD CONSTRAINT your_constraint
>> > FOREIGN KEY (referencing_column)
>> > REFERENCES referred_table (primary_key_column)
>> > ON CASCADE DELETE
>> >
>> >
>> > --
>> > Jacco Schalkwijk
>> > SQL Server MVP
>> >
>> >
>> > "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
>> > wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>> >>I have to SQL 2000 Server setups. It seems I have different builds on
>> >>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>> >>working one is 8.00.760. In the problem one I can not setup a
>> >>cascading
>> >>delete from Enterprise Manager. Anyone know why? Getting the problem
>> >>updated is going to be a bear. Anyone know a work around to add my
>> >>Cascading Delete?
>> >>
>> >> Chris
>> >>
>> >
>> >
>>|||Perhaps the database has compatibility level lower than 80?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote in message
news:%23HU5f$yAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Nope, same error message? Anyone know what's going on?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
>> ...
>> on delete cascade
>>
>> AMB
>> "Chris, Master of All Things Insignifican" wrote:
>> Ya I tried that too:
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
>> And get this error:
>> Server: Msg 156, Level 15, State 1, Line 4
>> Incorrect syntax near the keyword 'ON'.
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
>> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Just create your foreign key in T-SQL:
>> >
>> > ALTER TABLE your_table DROP CONSTRAINT your_constraint
>> > GO
>> > ALTER TABLE your_table ADD CONSTRAINT your_constraint
>> > FOREIGN KEY (referencing_column)
>> > REFERENCES referred_table (primary_key_column)
>> > ON CASCADE DELETE
>> >
>> >
>> > --
>> > Jacco Schalkwijk
>> > SQL Server MVP
>> >
>> >
>> > "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
>> > wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>> >>I have to SQL 2000 Server setups. It seems I have different builds on
>> >>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>> >>working one is 8.00.760. In the problem one I can not setup a cascading
>> >>delete from Enterprise Manager. Anyone know why? Getting the problem
>> >>updated is going to be a bear. Anyone know a work around to add my
>> >>Cascading Delete?
>> >>
>> >> Chris
>> >>
>> >
>> >
>>
>

Cascading Deletes

We have just upgraded from sql server 7 to sql server 2000.
The BOL talks about setting the cascading deletes
in different ways.
1. By setting the 'ON DELETE CASCADE' parameter of the
foreign key references clause in a Create Table or Alter Table
statement.
2. By Viewing the Manage Relationships Tab of the relationships
view in table design, and clicking on the 'Cascade Delete Related
Records' Checkbox.
Are these two methods the same thing?
1. talks about Contraints and 2. is refering to relaionships.
If they are not the same thing, How do I implement the cascade deletes
on existing tables, and on new tables?
Thank You for your help,
Laurence Nuttall
Programmer Analyst III
UCLA - Division of Continuing Educationthey are the same, as with ON UPDATE,
personally, i prefer to explictly delete the FK references
first, but that is just my preference so i don't delete
anything i didn't intend to, there are circumstance when
CASCADE ON UPDATE is required
>--Original Message--
>We have just upgraded from sql server 7 to sql server
2000.
>The BOL talks about setting the cascading deletes
>in different ways.
>1. By setting the 'ON DELETE CASCADE' parameter of the
> foreign key references clause in a Create Table or
Alter Table
> statement.
>2. By Viewing the Manage Relationships Tab of the
relationships
> view in table design, and clicking on the 'Cascade
Delete Related
> Records' Checkbox.
>
>Are these two methods the same thing?
>1. talks about Contraints and 2. is refering to
relaionships.
>If they are not the same thing, How do I implement the
cascade deletes
>on existing tables, and on new tables?
>Thank You for your help,
>Laurence Nuttall
>Programmer Analyst III
>UCLA - Division of Continuing Education
>
>.
>

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

Cascading Deletes

Nope, same error message? Anyone know what's going on?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
> ...
> on delete cascade
>
> AMB
> "Chris, Master of All Things Insignifican" wrote:
>Perhaps the database has compatibility level lower than 80?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote
in message
news:%23HU5f$yAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Nope, same error message? Anyone know what's going on?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>

Cascading Deletes

Thursday, March 8, 2012

Cascade deletes in SQL Server 7.0?

Is it possible to have data deleted(automaticly) from tables related to the one im deleting from..

Example:

Table A have a one to many relation to 6 other tables.

I will delete a member from Table A, will the data conserning the member in the other 6 tables be automaticly removed?

Best wishes.

(If not, how should I write the sp to handle this task?)For SQL Server 7.0 you would have to implement TRIGGERS to perform cascade deletes. However in SQL Server 2000 there is cascade deletes, defined by 'Cascading Referential Integrity Constraints'|||Okay!

Thanks for responding - Scre@.m

Cascade deletes in SQL 2005

I have a logins table, a loginroles table (intermediate), and a roles table

When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.

Any help would be appreciated.

Thank You,
Jason

It is there even in Express edition, you should see enable DRI(declarative referential integrity) at the top of Management Studio. BTW SQL Server 2005 also comes with the other two ANSI SQL definition DRI SET NULL and SET DEFAULT. Try the links below for more from Microsoft and my interaction with a user about SQL Server Express. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

http://forums.asp.net/thread/1116493.aspx

|||

Can you just walk me through how to find it? I am looking around but do not see it, What should I click on? Table, PK, FK? Right click? Properties? Contraints? I look but I do not see it...

|||

You are looking for enable relationship dialog box at the top of Management studio try the link below for all the options. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

cascade deletes and updates

Hi,
I was wondering whether cascaded deletes or updates would be
replicated to the subscriber. The subscriber tables are just plain
tables without any constraints. But the publisher tables have FK
relationships. On the publishers database, if I deleted a row from the
parent table resulting in the removal of the child rows, would the
child rows also get removed in the subscriber tables? Thanks
- Komp
If the child row table is replicated then it'll work as you propose.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||By default they are replicated. If you don't want this behavior right click
on your table in EM, select Design Table, and then select the properties
icon (looks like a hand pointing to something) and click on relationships.
Ensure that Enforce Relationship for Inserts and Updates is selected and
check cascading Update Related Fields.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<k0mput3r@.gmail.com> wrote in message
news:1140023314.624365.105580@.o13g2000cwo.googlegr oups.com...
> Hi,
> I was wondering whether cascaded deletes or updates would be
> replicated to the subscriber. The subscriber tables are just plain
> tables without any constraints. But the publisher tables have FK
> relationships. On the publishers database, if I deleted a row from the
> parent table resulting in the removal of the child rows, would the
> child rows also get removed in the subscriber tables? Thanks
> - Komp
>
|||Also ensure that the enforce relationship for replication is enabled as
well.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e9R8BllMGHA.2036@.TK2MSFTNGP14.phx.gbl...
> By default they are replicated. If you don't want this behavior right
> click on your table in EM, select Design Table, and then select the
> properties icon (looks like a hand pointing to something) and click on
> relationships. Ensure that Enforce Relationship for Inserts and Updates is
> selected and check cascading Update Related Fields.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <k0mput3r@.gmail.com> wrote in message
> news:1140023314.624365.105580@.o13g2000cwo.googlegr oups.com...
>

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Deletes

cascade delete ...

Hi!
I have a question about implementing cascade deletes in sql server 2000
database.
I have two tables:
Object (ObjectId, ObjectName, ObjectType, etc.)
Object_Association (ObjectParentId, ObjectId)
Both fields in the association table are foreign key related to the
ObjectId field in the first table. I would like the cascade delete on
both these relations.
So, if the tables had the following records:
1, horse, ..
2, cat, ..
3, cattle, ..
4, swine, ..
and
2, 1
3, 2
4, 2
and I delete the horse record in the object table, it should trigger
deleting the first record in the second table, that should trigger
delete of the cat record, ... and finally all the above records should
be deleted.
I get the following error when I try to set the cascade delete flags on
the foreign key relations in sql server 2000:
Introducing FOREIGN KEY constraint 'Object_Object_Association_FK2' on
table 'Object_Association' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
FOREIGN KEY constraints.
How do I achieve the complete cascade delete?
Thanks!kazoo wrote:
> Hi!
> I have a question about implementing cascade deletes in sql server
> 2000 database.
> I have two tables:
> Object (ObjectId, ObjectName, ObjectType, etc.)
> Object_Association (ObjectParentId, ObjectId)
> Both fields in the association table are foreign key related to the
> ObjectId field in the first table. I would like the cascade delete on
> both these relations.
> So, if the tables had the following records:
> 1, horse, ..
> 2, cat, ..
> 3, cattle, ..
> 4, swine, ..
> and
> 2, 1
> 3, 2
> 4, 2
> and I delete the horse record in the object table, it should trigger
> deleting the first record in the second table, that should trigger
> delete of the cat record, ... and finally all the above records should
> be deleted.
> I get the following error when I try to set the cascade delete flags
> on the foreign key relations in sql server 2000:
> Introducing FOREIGN KEY constraint 'Object_Object_Association_FK2' on
> table 'Object_Association' may cause cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
> FOREIGN KEY constraints.
> How do I achieve the complete cascade delete?
> Thanks!
That's a good question. I'm guessing SQL Server cannot handle this type
of delete cascade functionality, probably because of the the
self-referencing table relationship - it won't know which delete cascade
to perform first. You should manage the delete from a stored procedure
instead. From BOL:
"The series of cascading referential actions triggered by a single
DELETE or UPDATE must form a tree containing no circular references. No
table can appear more than once in the list of all cascading referential
actions that result from the DELETE or UPDATE. The tree of cascading
referential actions must not have more than one path to any given table.
Any branch of the tree is terminated when it encounters a table for
which NO ACTION has been specified or is the default."
David Gugick - SQL Server MVP
Quest Software

Monday, March 19, 2012

Cascading Updates / Delete Problem

Cascading Updates / Delete Problem

Cascading Updates / Delete Problem

Sunday, March 11, 2012

cascading deletes through a 1-M join table

Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

Cascading Deletes

Cascading Deletes

Cascading Deletes

Cascading Deletes

Cascading Deletes

Cascading Deletes

Thursday, March 8, 2012

Cascade deletes in SQL Server 7.0?

Cascade deletes in SQL 2005

cascade deletes and updates

Cascade Deletes

Cascade Deletes

Cascade Deletes

cascade delete ...

uninstall SQL

Blog Archive

About Me