Cannot trim table because it is referenced by a FOREIGN KEY constraint

Question

I get the following message, even if the table referencing it is empty: "Cannot trim table" dbo.Link "because the FOREIGN KEY constraint refers to it" It does not seem to make much sense, why is this happening, Any suggestions?

Answer 1

In SQL Server, the table referenced by FK cannot currently be truncated, even if all the referenced tables are empty or foreign keys are disabled.

You need to use DELETE (it might take a lot more logging) or abandon the relationship (s) before using TRUNCATE and subsequently recreate them or look at workarounds for this connection element to achieve this using ALTER TABLE ... SWITCH

Answer 2

You cannot trim a table that has an FK constraint. As a workaround, you can: 1 / Discard restrictions 2 / End table 3 / Restore restrictions.

Here it is a related T-SQL script, suppose you have 2 tables named MyTable and MyReferencedTable :

 -- Remove constraint IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable') BEGIN ALTER TABLE dbo.MyReferencedTable DROP CONSTRAINT FK_MyReferencedTable_MyTable END -- Truncate table TRUNCATE TABLE dbo.MyTable -- Re-Add constraint IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable') BEGIN ALTER TABLE dbo.MyReferencedTable WITH CHECK ADD CONSTRAINT [FK_MyReferencedTable_MyTable] FOREIGN KEY(ListingKey) REFERENCES dbo.MyTable (ListingKey) END 

Answer 3

Run the following query to search for any constraint:

 use MyDatabase select c.name as c_name, t.name as t_name from sys.key_constraints c join sys.tables t on t.object_id = c.parent_object_id 

If any constraint is found in your table, delete it.

Answer 4

If you get this error and you need to trim the table, an alternative solution would be to reset and recreate the table along with primary/other_keys/indexes/triggers . Make sure you do not need the data in this table.

This soul acts like a charm to me and is unlikely to take a minute to complete. I do this for disguise.

Answer 5

Instead of deleting or re-creating the constraint, I prefer this simpler way. Disable constraint checking by first executing the following query:

 SET FOREIGN_KEY_CHECKS=0; 

Then crop your tables

Finally, activate the constraint check:

 SET FOREIGN_KEY_CHECKS=1; 

This is a common solution when migrating databases, so you don’t have to worry about how tables are inserted.