Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

Question

I get the following message even when the table that references it is empty: "Cannot truncate table 'dbo.Link' because it is being referenced by a FOREIGN KEY constraint" Doesn't seem to make much sense why this is occurring. Any suggestions?

Answer 1

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

You need to use DELETE (may require much more logging) or drop the relationship(s) prior to using TRUNCATE and recreate them afterwards or see the workarounds on this connect item for a way of achieving this using ALTER TABLE ... SWITCH

Answer 2

You cannot truncate a table which has an FK constraint on it. As workaround, you could: 1/ Drop the constraints 2/ Trunc the table 3/ Recreate the constraints.

Here it is the associated T-SQL script, supposing you have 2 tables called 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