Cascade Delete

Is there a way to turn off cascade delete temporarily? Yes, I want to leave
orphan records in this instance. I tried the following:
ALTER TABLE tForm NOCHECK CONSTRAINT
Delete From tformDerek Hart (derekmhart@.yahoo.com) writes:
> Is there a way to turn off cascade delete temporarily? Yes, I want to
> leave orphan records in this instance. I tried the following:
> ALTER TABLE tForm NOCHECK CONSTRAINT
> Delete From tform
You would have to disable the constraints on the referring tables, not
the table you are deleting from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Why' I see that you name tables with a "t-" prefix, so I am pretty
sure that you do not understada how to design an RDBMS. But that is
only based on 20+ years of education and experience in RDBMS and SQL.
Can you explain this?|||Naming tables with a 't' prefix means I don't know how to design an RDBMS.
Well, my commercial application that I sell for $100,000 that has 110 tables
and 1500 stored procedures sure must be having problems!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1146871618.195792.312780@.y43g2000cwc.googlegroups.com...
> Why' I see that you name tables with a "t-" prefix, so I am pretty
> sure that you do not understada how to design an RDBMS. But that is
> only based on 20+ years of education and experience in RDBMS and SQL.
> Can you explain this?
>|||Since you want to leavew orphans for some reason, yes, it probalby is
havngh problems -- like orphans! That is what indicates that you don't
know how to design an RDBMS. The leading "t-" and other things are
just symptoms of OO design, ignorance of ISO Standards, etc.
And I have worked for companies in the dor-bomb era that cost muxh more
and were much larger. The reason that the databases were so large was
poor design and lots of orphans -- both tables and rows.|||Orphans are only available for seconds until all the data is filled up in
the database. This conversation is complete.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1146887407.304043.143670@.u72g2000cwu.googlegroups.com...
> Since you want to leavew orphans for some reason, yes, it probalby is
> havngh problems -- like orphans! That is what indicates that you don't
> know how to design an RDBMS. The leading "t-" and other things are
> just symptoms of OO design, ignorance of ISO Standards, etc.
> And I have worked for companies in the dor-bomb era that cost muxh more
> and were much larger. The reason that the databases were so large was
> poor design and lots of orphans -- both tables and rows.
>|||Derek Hart (derekmhart@.yahoo.com) writes:
> Orphans are only available for seconds until all the data is filled up in
> the database. This conversation is complete.
You mean that for several seconds you commit a breach against the
principles of database design and insult Joe Celko! How dare you?
On a more serious note, when you reapply the constraint, be sure to
use this funny syntax:
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT fk_xxxx
WITH CHECK instructs SQL Server to actually validate the constraint.
If you leave it out, you get WITH NOCHECK. This is obviously faster,
but there is a price to pay: If the constraint is reenabled WITH
NOCHECK, the constraint is not trusted as far as the optimizer is
concerned. This can lead to poorer execution plans, as the optimizer
can not use the constraint to rule out certain conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am doing a table update of over 100 tables. I apply this when I begin:
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And when I am done I apply
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
I believe this properly re-enables the constraints, but does it lead to
poorer execution plans.
And did you mean to have CHECK CHECK CONSTRAINT or just one CHECK?
Derek Hart
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BBC7AA7F30Yazorman@.127.0.0.1...
> Derek Hart (derekmhart@.yahoo.com) writes:
> You mean that for several seconds you commit a breach against the
> principles of database design and insult Joe Celko! How dare you?
> On a more serious note, when you reapply the constraint, be sure to
> use this funny syntax:
> ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT fk_xxxx
> WITH CHECK instructs SQL Server to actually validate the constraint.
> If you leave it out, you get WITH NOCHECK. This is obviously faster,
> but there is a price to pay: If the constraint is reenabled WITH
> NOCHECK, the constraint is not trusted as far as the optimizer is
> concerned. This can lead to poorer execution plans, as the optimizer
> can not use the constraint to rule out certain conditions.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||IMO it might be easier to insert new parent rows, redirect the child
rows to their new parents, and delete old parents. Also it might be way
faster then re-enabling a constraint on a big table.|||Derek Hart (derekmhart@.yahoo.com) writes:
> I am doing a table update of over 100 tables. I apply this when I begin:
> SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> And when I am done I apply
> SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
> I believe this properly re-enables the constraints, but does it lead to
> poorer execution plans.
This reenables the constraint without verifying that existing data
complies with the constraint.

> And did you mean to have CHECK CHECK CONSTRAINT or just one CHECK?
The syntax is indeed
as I wrote. Just check Books Online. :-) Yes, it's really a horrible
syntax.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx