Forum Discussion
Issue Truncating Table with Foreign Key Constraints in Microsoft SQL Server 2022
Hi everyone, I'm currently working with Microsoft SQL Server 2022, and I'm encountering an issue when trying to truncate a table. The error message I'm receiving is as follows: "Cannot truncate ...
Hi there.
You can't do what you're trying to do:
Additionally, your command for disabling the checks isn't helping because that will only impact the tables containing the foreign key definitions, not the table you're trying to truncate.
For example, let's assume you have three tables:
| table1 | The main table you're trying to truncate. |
| table2 | Contains foreign key references to table1. |
| table3 | Contains foreign key references to table1. |
- You then run your sp_msforeachtable statement;
- You then run an INSERT or UPDATE against table2, inserting/updating the FK column to a value that does not exist in table1;
- This succeeds because you disabled the constraint via sp_msforeachtable;
- You then try to TRUNCATE table1 which fails with the "is being referenced by a FOREIGN KEY constraint" error, which is expected.
You can see from this that the sp_msforeachtable statement isn't doing what you wanted it to do since it's only impacting operations on tables 2 and 3, not table1 (since you cannot prevent the checking of foreign key definitions - you'd have to drop them.)
Cheers,
Lain