Deleting all rows from table with RI

Many thanks for the responses however I didn't clarify one
important issue:
The tables in question were created without this option
yet have corresponding FK values in other tables. My job
is to delete all data in these tables. If I attempt to do:
DELETE FROM TABLE XYZ it fails complaining about
constraints. I can't find any examples of syntax such as
DELETE FROM TABLE XYZ WITH CASCADE which I thought would
be appropriate. How do I go about deleting all data
within these tables. I know I can simply delete all the
data in the referencing tables prior to doing the
reference tables or drop the constraints but I don't want
to do this if possible?
TIA
GRANT|
| Many thanks for the responses however I didn't clarify one
| important issue:
| The tables in question were created without this option
| yet have corresponding FK values in other tables. My job
| is to delete all data in these tables. If I attempt to do:
| DELETE FROM TABLE XYZ it fails complaining about
| constraints. I can't find any examples of syntax such as
| DELETE FROM TABLE XYZ WITH CASCADE which I thought would
| be appropriate. How do I go about deleting all data
| within these tables. I know I can simply delete all the
| data in the referencing tables prior to doing the
| reference tables or drop the constraints but I don't want
| to do this if possible?
--
Hi Grant,
I apologise if I misunderstand your real requirements. You said you want to
delete all data in these tables? Would it be feasible to just TRUNCATE
TABLE the foreign table before TRUNCATEing TABLE the primary table next?
TRUNCATE TABLE would be more efficient because it is not logged.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||You can't TRUNCATE a table if it is referenced by a foreign key, but you can
still use DELETE FROM <table name> if you clear out the referencing table
first.
The simplest thing to do is generate a database diagram in Enterprise
Manager, write down the tables that reference table XYZ (and the tables that
reference those tables etc), start with deleting from the last one and work
your way up.
--
Jacco Schalkwijk
SQL Server MVP
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:E25pB%23uvDHA.3532@.cpmsftngxa07.phx.gbl...
> |
> | Many thanks for the responses however I didn't clarify one
> | important issue:
> | The tables in question were created without this option
> | yet have corresponding FK values in other tables. My job
> | is to delete all data in these tables. If I attempt to do:
> | DELETE FROM TABLE XYZ it fails complaining about
> | constraints. I can't find any examples of syntax such as
> | DELETE FROM TABLE XYZ WITH CASCADE which I thought would
> | be appropriate. How do I go about deleting all data
> | within these tables. I know I can simply delete all the
> | data in the referencing tables prior to doing the
> | reference tables or drop the constraints but I don't want
> | to do this if possible?
> --
> Hi Grant,
> I apologise if I misunderstand your real requirements. You said you want
to
> delete all data in these tables? Would it be feasible to just TRUNCATE
> TABLE the foreign table before TRUNCATEing TABLE the primary table next?
> TRUNCATE TABLE would be more efficient because it is not logged.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||Many thanks to both...that answers my question ie no it can't be done as a simple one-off delete statement. I guess that has its advantages in preventing mishaps!
Cheers
Grant
>--Original Message--
>You can't TRUNCATE a table if it is referenced by a foreign key, but you can
>still use DELETE FROM <table name> if you clear out the referencing table
>first.
>The simplest thing to do is generate a database diagram in Enterprise
>Manager, write down the tables that reference table XYZ (and the tables that
>reference those tables etc), start with deleting from the last one and work
>your way up.
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"Eric Cardenas" <ecard@.anonymous.com> wrote in message
>news:E25pB%23uvDHA.3532@.cpmsftngxa07.phx.gbl...
>> |
>> | Many thanks for the responses however I didn't clarify one
>> | important issue:
>> | The tables in question were created without this option
>> | yet have corresponding FK values in other tables. My job
>> | is to delete all data in these tables. If I attempt to do:
>> | DELETE FROM TABLE XYZ it fails complaining about
>> | constraints. I can't find any examples of syntax such as
>> | DELETE FROM TABLE XYZ WITH CASCADE which I thought would
>> | be appropriate. How do I go about deleting all data
>> | within these tables. I know I can simply delete all the
>> | data in the referencing tables prior to doing the
>> | reference tables or drop the constraints but I don't want
>> | to do this if possible?
>> --
>> Hi Grant,
>> I apologise if I misunderstand your real requirements. You said you want
>to
>> delete all data in these tables? Would it be feasible to just TRUNCATE
>> TABLE the foreign table before TRUNCATEing TABLE the primary table next?
>> TRUNCATE TABLE would be more efficient because it is not logged.
>> Hope this helps,
>> --
>> Eric C=E1rdenas
>> SQL Server support
>
>.
>|||If you change your foreign keys to - on delete cascade
you will be able to do it...
Bojidar Alexandrov

Deletes

I want to clarify something regarding the deletes. On Publisher, we have a
table which has foreign keys. I checked the same table in subscriber. It
does not have foreign keys but it has primary and unique keys. Does
Replication ensure that delete happen in the same order on all subscribers as
they do in Publisher otherwise we can end up with orphans in subscribers.
For deletes, we are replicating the execution of delete stored procedure.
Can someone please clarify this?
Thanks
For transaction, yes it does as long as cascading updates and deletes are
enforced for replication.
With merge it is best to make the constraints not for replication.
Hilary Cotter
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
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:F2C6E011-F9BF-4DC6-9346-71563E93E25D@.microsoft.com...
>I want to clarify something regarding the deletes. On Publisher, we have a
> table which has foreign keys. I checked the same table in subscriber. It
> does not have foreign keys but it has primary and unique keys. Does
> Replication ensure that delete happen in the same order on all subscribers
> as
> they do in Publisher otherwise we can end up with orphans in subscribers.
> For deletes, we are replicating the execution of delete stored procedure.
> Can someone please clarify this?
>
> Thanks
>