Can You Truncate a Table with Foreign Key Constraints?

Learn how to automatically truncate tables with foreign key constraints in an automated way by writing a script to store create statements, execute drop constraint statements, truncate the table and then re-create the constraints.

Charlotte Wilson16/01/231 minute read0 Comments

105.3k

You cannot truncate a table that has a foreign key constraint. To do so, you must delete and recreate the constraints. This is fine for smaller tables, but if the table has millions of rows, a truncation is much faster. To automate this process, you can write a script to store create statements, execute drop constraint statements, truncate the table, and then re-create the constraints.

Unfortunately, disabling restrictions is not enough. You cannot truncate a table to which FK constraints have been applied (TRUNCATE is not the same as DELETE). The cost of writing to the transaction log is too high, and setting up the entire database in SIMPLE recovery mode to truncate a table is ridiculous. I just found out that you can use the TRUNCATE table in a parent table with foreign key constraints on a child as long as you DISABLE the constraints on the child table first.

To demonstrate this error, let's first create a demo database “SQLHintStruncateDemo”, two Client and Order tables. The process removes the foreign key constraint and the truncated table, and then adds constraint following the steps. Based on the result, the truncation of the customer table is failing, because the CusTid column in the orders table refers to the CusTid column of the orders table. Msg 4712, Level 16, State 1, Line 1 The “Customer” table cannot be truncated because it is referenced by a FOREIGN KEY constraint. I like your solution for truncating tables with foreign keys, but it doesn't work properly with tables that have complex primary keys.

A friend talked about a truncated cascade for this case, but I haven't found any related information, also another user told me to try; I tried, but I'm not truncating my tables yet. If you want to truncate a table with foreign key constraints in an automated way, you must write a script to store create statements, execute drop constraint statements, truncate the table, and then re-create the constraints. This will ensure that your data remains intact while still allowing you to quickly remove large amounts of data from your tables.