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
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
CustomersthatOrdersreferences. - A reset script that truncates tables to reload test data.
How to fix it
-
Use
DELETEinstead (logged row by row, slower on big tables).DELETE FROM dbo.Orders; DELETE FROM dbo.Customers;
-
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
3726Can't drop: referenced by a FOREIGN KEY547Statement conflicted with a constraint1701MySQL: Can't truncate a table with foreign keysORA-02266Oracle: keys referenced by enabled foreign keys
More constraints and keys errors
2627Violation of PRIMARY KEY or UNIQUE KEY2601Cannot insert duplicate key row515Cannot insert the value NULL into column1913Index or statistics name already exists1750Could not create constraint or index1505Duplicate key found, unique index
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.
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.