SQL Server error 3726: 'Could not drop object because it is referenced by a FOREIGN KEY constraint'

Could not drop object 'dbo.Courses' because it is referenced by a FOREIGN KEY constraint. (error 3726) means another table has a foreign key pointing at the table you are dropping. Drop that foreign key first, or drop the referencing table first, then drop this one.

The message does not name the foreign key. A short query on sys.foreign_keys, shown below, lists every key that points at the table and the table it lives in.

The error

DROP TABLE dbo.Courses;

Msg 3726, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Could not drop object 'dbo.Courses' because it is referenced by a FOREIGN KEY constraint.

Why it happens

dbo.Enrolments had FK_Enrolments_Courses, a foreign key from CourseId to dbo.Courses (Id). Dropping Courses would leave that key pointing at nothing, so SQL Server refuses. It refuses even when both tables are empty, as they were in the test: the check is about the constraint, not the rows. Teardown scripts and reset scripts hit this most, because they drop tables in creation order, parent first.

To see who references the table:

SELECT name AS ForeignKey, OBJECT_NAME(parent_object_id) AS ReferencingTable
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(N'dbo.Courses');

ForeignKey ReferencingTable
---------- ----------------
FK_Enrolments_Courses Enrolments

The fix

-- fix A: drop the foreign key first, then the table
ALTER TABLE dbo.Enrolments DROP CONSTRAINT FK_Enrolments_Courses;
DROP TABLE dbo.Courses;
SELECT OBJECT_ID(N'dbo.Courses') AS CoursesObjectId;

OBJECT_ID then returned NULL: the table was gone, and Enrolments stayed, now without the key. Use fix A when the child table should remain. When both tables are going, drop the child first. After recreating the pair, this removed both in one statement, children listed before parents:

DROP TABLE dbo.Enrolments, dbo.Courses;
SELECT OBJECT_ID(N'dbo.Courses') AS CoursesObjectId, OBJECT_ID(N'dbo.Enrolments') AS EnrolmentsObjectId;

Both object ids came back NULL. For a reset script, list tables in reverse creation order and the error does not appear.

Before choosing fix A on a real database, think about what the foreign key was protecting. Once it is dropped, nothing stops Enrolments from holding course ids that no longer exist, so either drop or archive those rows too, or recreate the key against the replacement table. Run the sys.foreign_keys query again afterwards: an empty result means nothing else still points at the table you are about to drop.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e3726.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. Two empty tables, courses and enrolments, joined by one foreign key. The server's computer name in the message is replaced by MACHINE; nothing else changed.

Frequently asked

What does 'Could not drop object because it is referenced by a FOREIGN KEY constraint' mean?

SQL Server error 3726 means another table has a foreign key that points at the table you are dropping, so dropping it would leave that key pointing at nothing.

How do I find which foreign key references a table in SQL Server?

Query sys.foreign_keys where referenced_object_id = OBJECT_ID('dbo.YourTable') and show OBJECT_NAME(parent_object_id) to get the referencing table.

How do I drop a table that is referenced by a foreign key?

Drop the foreign key with ALTER TABLE ... DROP CONSTRAINT, then drop the table. If the referencing table is going too, drop it first, or list it before the parent in one DROP TABLE.

More decoded errors in the Fixes category, including error 1785, multiple cascade paths, another foreign-key refusal.