You can't truncate a table that has foreign key constraints. A foreign key (FK) is a column or combination of columns that is used to establish and apply a link between data in two tables. It can be created by defining a FOREIGN KEY constraint when creating or modifying a table. You will need to delete and recreate the constraints, so be sure to write them down before deleting them.
The TRUNCATE TABLE statement, similar to the DELETE statement without a WHERE clause, deletes all rows in a table. If the table being truncated contains an IDENTITY column, the counter for that column is reset to the initial value defined for the column. To demonstrate this error, let's first create a demo database “SQLHintStruncateDemo”, two Client and Order tables. But unlike the DELETE statement, the TRUNCATE TABLE statement does not record deletions of individual rows, which makes it faster and uses fewer system logging and transaction resources.
If you want it to be more automated with your truncation; you would have to find a way to store create statements, execute drop constraint statements, truncate your table, then go back and execute create statements. 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. In conclusion, you cannot truncate a table that has an FK constraint.
Leave Reply