How to delete all data from table when foreign-key exists.

If you want an easy way which doesn't involve dropping the
constraints, you could use truncate table on all the
tables, but being careful about the order - apply the
command from the outside inwards relative to your schema
eg do the lookup tables first and the most central table
last.
Regards,
Paul IbisonPaul,
I'm afraid that truncate isn't allowed event if you have 0 rows in the
referencing tables. And even if you disable the FK, it isn't allowed. I
usually truncate the tables "at the bottom", these are typically the ones
with the most rows in. Then I just work my way "upward" and do DELETE on
those tables.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote in message
news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
> If you want an easy way which doesn't involve dropping the
> constraints, you could use truncate table on all the
> tables, but being careful about the order - apply the
> command from the outside inwards relative to your schema
> eg do the lookup tables first and the most central table
> last.
> Regards,
> Paul Ibison|||Thanks Tibor - had forgotten this behaviour.
Regards,
Paul Ibison

>--Original Message--
>Paul,
>I'm afraid that truncate isn't allowed event if you have
0 rows in the
>referencing tables. And even if you disable the FK, it
isn't allowed. I
>usually truncate the tables "at the bottom", these are
typically the ones
>with the most rows in. Then I just work my way "upward"
and do DELETE on
>those tables.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote
in message
>news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
the
>
>.
>