Cannot Truncate Table – Foreign Key Constraint Blocking You

That moment when you run TRUNCATE TABLE orders and get hit with a foreign key violation. Annoying. Here's the direct fix, then the explanation.

The Quick Fix

Disable the referencing foreign key, truncate, re-enable. In Oracle:

ALTER TABLE order_items DISABLE CONSTRAINT fk_order_id;
TRUNCATE TABLE orders;
ALTER TABLE order_items ENABLE CONSTRAINT fk_order_id;

Same idea in SQL Server:

ALTER TABLE order_items NOCHECK CONSTRAINT fk_order_id;
TRUNCATE TABLE orders;
ALTER TABLE order_items CHECK CONSTRAINT fk_order_id;

PostgreSQL:

ALTER TABLE order_items DISABLE TRIGGER ALL;
TRUNCATE TABLE orders;
ALTER TABLE order_items ENABLE TRIGGER ALL;

MySQL:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
SET FOREIGN_KEY_CHECKS = 1;

That's it. Done in 3 lines.

Why This Works

What's actually happening here is that TRUNCATE is a DDL operation, not DML. Unlike DELETE, which checks each row and fires triggers, TRUNCATE deallocates the data pages in one shot. The database engine sees the foreign key constraint and says "nope, can't nuke this parent table – orphaned child rows would violate referential integrity."

The reason step 3 works is that by the time you re-enable the constraint, the child table has zero rows referencing the parent. No orphan data, no violation. The constraint only checks existing data when you enable it (unless you use VALIDATE in Oracle).

When the Simple Fix Doesn't Work

You have a self-referencing constraint

If a table has a foreign key pointing to itself (like an employee table where manager_id references employee_id), you can't disable it alone. You need to null out the column first:

UPDATE employees SET manager_id = NULL;
TRUNCATE TABLE employees;

Then repopulate and update managers.

You have a circular foreign key dependency

Table A references B, B references C, C references A. Disabling one doesn't help – you need to disable all three, truncate in reverse order, then enable. Or use DELETE with a loop.

You're in Oracle and the constraint is part of a materialized view log

Some materialized view logs create hidden constraints. Check USER_CONSTRAINTS for system-generated names. You might need to drop and recreate the MV log.

Better Approach: Use DELETE When You Must

If you're working with a small table (under 100k rows), DELETE with a WHERE clause avoids the whole mess:

DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE created_date < '2024-01-01');
DELETE FROM orders WHERE created_date < '2024-01-01';

But DELETE generates undo logs, fires triggers, and is slower. For big tables, the disable/truncate method is better.

Prevention

Three things to stop this from biting you again:

1. Use ON DELETE CASCADE sparingly. It auto-deletes children when you delete a parent, but it doesn't help with TRUNCATE – TRUNCATE doesn't fire the cascade.
2. Write a stored procedure that disables all foreign keys on a table, truncates, and re-enables. Here's a SQL Server version:

CREATE PROCEDURE TruncateTableWithForeignKeys
    @TableName NVARCHAR(128)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SELECT @SQL = STRING_AGG('ALTER TABLE ' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
        + ' NOCHECK CONSTRAINT ' + QUOTENAME(f.name), '; ')
    FROM sys.foreign_keys f
    WHERE OBJECT_NAME(f.referenced_object_id) = @TableName;

    EXEC sp_executesql @SQL;

    SET @SQL = 'TRUNCATE TABLE ' + QUOTENAME(@TableName);
    EXEC sp_executesql @SQL;

    SELECT @SQL = STRING_AGG('ALTER TABLE ' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
        + ' CHECK CONSTRAINT ' + QUOTENAME(f.name), '; ')
    FROM sys.foreign_keys f
    WHERE OBJECT_NAME(f.referenced_object_id) = @TableName;

    EXEC sp_executesql @SQL;
END

That's it. Stop fighting the constraint – disable it, do your work, turn it back on.