What is the equivalent effect to Truncating a table, when the table is referenced by a foreign-key

Question

Straight out of the MSDN docs for Sql Server 2005:

You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint.
• Participate in an indexed view.
• Are published by using transactional replication or merge replication.

I want the effect of a TRUNCATE (specifically the fact that it resets IDENTITY type columns), but I can't use one in my case because my table is referenced by a foreign-key elsewhere in the database.

Update: This is for a testing configuration where I am clearing out the referencing table as well, so foreign-key integrity is a non-issue.

What other ways are there to get around this?

Answer 1

You can delete all the rows and then do a DBCC CHECKIDENT (Tablename, RESEED, 0) to reset the identity seed

But again DELETE is fully logged while TRUNCATE is minimally logged and will be many times faster

Another option is to drop the foreign key constrains then do a truncate and then recreating the foreign key constraints

Answer 2

You can drop the foreign key, truncate the table, and then recreate the foreign key.

Answer 3

You will need to drop the constraint, trunc the table, and then add the constraint back. However, you should be very careful with this. If there are rows in the table that you are dropping the FK reference to you will not be able to add it until those rows are deleted or the FK column in the other table is clear.

Answer 4

The fact that it is refernced by a foreign key is the clue you need to not truncate a table or you would be creating orphaned records. This is why truncate table is not allowed if a foreign key exists.

The correct process is to first delete the refernced records, if any, then drop the FK constraint, then truncate the table then reinstate the fk constraint. If you skip step one, you will create a data integrity nightmare where the record pointing to oldid 100 is not pointing to the new record that happens to get assigned to 100 and it isn;t the record it should match to.