// SQL Server error reference

SQL Server error 4712: Can't truncate a referenced table

TRUNCATE TABLE isn't allowed on a table that a foreign key references, even if the child table is empty. Use DELETE or drop the constraint.

Error number
4712
Severity
16
Category
Constraints and keys
Error message
Cannot truncate table '%.*ls' because it is being referenced by a FOREIGN KEY constraint.

What it means

TRUNCATE TABLE deallocates pages without checking rows one by one, so SQL Server can't verify foreign keys during it. It therefore refuses to truncate any table that is referenced by a foreign key constraint, whether or not child rows exist.

Common causes

  • Truncating a parent table such as Customers that Orders references.
  • A reset script that truncates tables to reload test data.

How to fix it

  1. Use DELETE instead (logged row by row, slower on big tables).

    DELETE FROM dbo.Orders;
    DELETE FROM dbo.Customers;
  2. Or drop the foreign keys, truncate, and recreate them.

    ALTER TABLE dbo.Orders DROP CONSTRAINT FK_Orders_Customers;
    TRUNCATE TABLE dbo.Orders;
    TRUNCATE TABLE dbo.Customers;
    ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId) REFERENCES dbo.Customers (CustomerId);

Related errors

More constraints and keys errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.