Articles

How to drop a table that is referenced by a FOREIGN KEY constraint

You try to drop a table and get:

Msg 3726, Level 16, State 1
Could not drop object 'dbo.Customer' because it is referenced by a FOREIGN KEY constraint.

Some other table has a foreign key pointing at dbo.Customer, and SQL Server will not leave that key pointing at nothing. The message does not say which table, and unlike Oracle and PostgreSQL, SQL Server has no DROP TABLE … CASCADE to take the constraints with it. You have to find them.

Find what points at the table

The constraint lives on the other table, which is why reading the definition of the table you want to drop does not show it. Ask the catalog instead:

SELECT
    fk.name                                        AS foreign_key,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' +
    OBJECT_NAME(fk.parent_object_id)               AS referencing_table
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.Customer');

A table can reference itself, for example an Employee table with a ManagerId column. That key shows up in the list too, and it is not a problem: dropping the table drops its own keys with it.

Decide what should happen to those tables

This is the real decision, and the error is forcing you to make it.

The referencing tables stay. Drop the foreign keys, then the table. The other tables keep their rows, and their columns keep values that now point at nothing. That is legal, and sometimes right, but it is rarely what anyone intends.

ALTER TABLE dbo.[Order]       DROP CONSTRAINT FK_Order_Customer;
ALTER TABLE dbo.CustomerNote  DROP CONSTRAINT FK_CustomerNote_Customer;

DROP TABLE dbo.Customer;

The referencing tables go too. Drop them first, children before parents. Dropping a table takes its own foreign keys with it, so once the children are gone nothing points at the parent any more.

DROP TABLE dbo.CustomerNote;
DROP TABLE dbo.[Order];     -- only if nothing references Order in turn
DROP TABLE dbo.Customer;

That last comment is where this gets slow. Order may be referenced by OrderLine, and OrderLine by something else, so run the query again for every table you add to the list. The chain is often longer than expected.

Generating the drops

For the first case, you can generate the statements from the same query and read them before running anything:

SELECT
    'ALTER TABLE ' +
    QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' +
    QUOTENAME(OBJECT_NAME(fk.parent_object_id)) +
    ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.Customer')
  AND fk.parent_object_id <> fk.referenced_object_id;

Paste the output into a new window, check it, and run it with the DROP TABLE in one transaction.

Before you run it

Script out the foreign keys first, even ones you are sure you do not need. Recreating a dropped key from memory means getting its columns, its name and its ON DELETE rule exactly right, and a missing key only shows itself when bad data turns up months later.

Seeing it before you start

In WoodFireERD, select the table and set Follow to Referenced by (others → this). The diagram shows every table with a foreign key pointing at it, and follows the chain further out as many levels as you ask for. When you choose Delete table, the confirmation names each foreign key that will be removed and the table it belongs to, before anything happens. The migration script drops those keys before the table, in the order SQL Server needs.

An unhandled error has occurred. Reload 🗙