TRUNCATE TABLES with CONSTRAINTS

Question

Is there a way to truncate tables with constraints ?

I tried to DEACTIvATE with this:

DECLARE @SQLtxt varchar(max)
SET @SQLtxt = '-- DESACTIVER LES CONTRAINTES' + CHAR(10)
SELECT @SQLtxt = @SQLtxt + 'ALTER TABLE [' + name + '] NOCHECK CONSTRAINT ALL;' FROM sys.tables
PRINT @SQLtxt

Of course, it didn't worked. I have to drop the constraint then recreate them ! The only way I could make it work is by extracting the script to drop and recreate the contraint.

Is there another way ? BTW I don't want to delete because it would use the Transaction Log.

Answer 1

You sort of answered the question yourself - if there's a foreign key referencing your table, SQL Server needs that information in the transaction log. Since TRUNCATE TABLE effectively bypasses the log, it's not allowed on tables referenced by foreign keys.

You'll either have to DROP the foreign key constraint, or use a DELETE statement.

Answer 2

It's only referencing (eg the REFERENCES bit) foreign keys you need to drop.

This should make it easier...

Answer 3

The TRUNCATE command will not work on tables with FK references. Straight from the documentation:

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

Answer 4

Here is a script that may help you get going scripting out FK's. Script out your foreign keys.

I use a modified version to dump the constraint definitions into a temp table, then do the TRUNCATE magic and then recreate the constraints from the temp table. However, this is only for my own convenience when restoring the production database onto a non-production environment to get rid of most of the data. Not sure, I would use it in a production scenario though. I would rather prefer deleting in small batches knowing that everything is fully logged.

Btw, womb's reference to the SQL Server 2000 Books Online is a bit misleading. TRUNCATE TABLE has always been a minimally logged operation.

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

This is has specified more precisely in later versions of Books Online.