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