SQL Server error 3726: Can't drop: referenced by a FOREIGN KEY
DROP TABLE failed because another table's foreign key references it. Drop the referencing constraint (or that table) first.
- Error number
3726- Severity
- 16
- Category
- Constraints and keys
What it means
SQL Server won't drop a table that is the parent of a foreign key, because the child table's constraint would point to nothing. The message names the table; the foreign keys that reference it are listed in sys.foreign_keys. TRUNCATE TABLE on such a table fails too, with error 4712.
Common causes
- Dropping tables in the wrong order in a teardown script.
- Recreating a table during a migration while other tables still reference it.
How to fix it
-
Find the foreign keys that reference the table.
SELECT fk.name, OBJECT_SCHEMA_NAME(fk.parent_object_id) AS child_schema, OBJECT_NAME(fk.parent_object_id) AS child_table FROM sys.foreign_keys AS fk WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.Customers'); -
Drop those constraints (or the child tables) first, then the table.
ALTER TABLE dbo.Orders DROP CONSTRAINT FK_Orders_Customers; DROP TABLE dbo.Customers;
-
In teardown scripts, drop child tables before parents.
Related errors
547Statement conflicted with a constraint3701Cannot drop, object not found1451MySQL: Foreign key fails: cannot delete parent rowORA-02449Oracle: unique/primary keys referenced by foreign keys2BP01PostgreSQL: dependent objects still exist
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 index4712Can't truncate a referenced table1505Duplicate 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.