How to Truncate a Table Referenced in a Foreign Key Constraint

You cannot truncate a table to which foreign key (FK) constraints have been applied. TRUNCATE TABLE is not the same as DELETE, and SQL Server will not allow a truncation if there is a foreign key constraint on the table. To successfully truncate such a table, you must first delete and recreate the constraints. It is important to write down the constraint definitions before deleting them.

I have found that the best way to do this is by getting the constraint definitions from a pair of Dynamic Management Views (DMVs) and storing the relevant data in a temporary table. Your solution for truncating tables with foreign keys may work well for simple primary keys, but it does not work properly with tables that have complex primary keys. To demonstrate this error, let's create a demo database “SQLHintStruncateDemo”, with two Client and Order tables. The result of the truncation of the customer table will fail because the CusTid column in the orders table refers to the CusTid column of the orders table.

To avoid recording this operation in the transaction log, I wanted to use the truncate operation instead of deleting. To successfully truncate such a table, you must first delete and recreate all foreign key constraints that refer to it. After removing all foreign key constraints, the table truncation should be successful. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only page deallocations in the transaction log.