SQL Server - Drop All References to Parent Table

In SQL Server, you cannot drop a table if it is referenced by a FOREIGN KEY constraint. You have to either drop the child tables before removing the parent table, or remove foreign key constraints.

This article provides a Transact-SQL script to drop foreign keys that can be helpful when you do not need to drop the child tables.

Last Update: Microsoft SQL Server 2012

Assume there is a parent and child tables in SQL Server:

   -- Parent table
   CREATE TABLE states
   ( 
       id CHAR(2) PRIMARY KEY,
       name VARCHAR(70)
   ); 
 
  -- Child table
  CREATE TABLE cities
   ( 
       name VARCHAR(70),
       state CHAR(2) REFERENCES states
   );

Now if you try to remove the parent table states, the DROP TABLE statement fails:

  DROP TABLE states;
  # Msg 3726, Level 16, State 1, Line 1
  # Could not drop object 'states' because it is referenced by a FOREIGN KEY constraint.

For a given parent table, you can use the following query to get foreign key constraint names and the referencing table names:

   -- Find foreign keys referencing to dbo.states table
   SELECT name AS 'Foreign Key Constraint Name', 
	       OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
   FROM sys.foreign_keys 
   WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
              OBJECT_NAME(referenced_object_id) = 'states'

Output:

Now you can alter the child table and drop the constraint by its name using the following statement:

  -- Drop the foreign key constraint by its name 
  ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

You can also use the following Transact-SQL script to automatically find and drop all foreign key constraints referencing to the specified parent table:

  BEGIN
 
  DECLARE @stmt VARCHAR(300);
 
  -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements  
  DECLARE cur CURSOR FOR
     SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) +
                    ' DROP CONSTRAINT ' + name
     FROM sys.foreign_keys 
     WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
                OBJECT_NAME(referenced_object_id) = 'states';
 
   OPEN cur;
   FETCH cur INTO @stmt;
 
   -- Drop each found foreign key constraint 
   WHILE @@FETCH_STATUS = 0
     BEGIN
       EXEC (@stmt);
       FETCH cur INTO @stmt;
     END
 
  CLOSE cur;
  DEALLOCATE cur;
 
  END
  GO

Now you can drop the parent table:

  DROP TABLE states;
  # Command(s) completed successfully.

• SQL Server Technical Articles

Microsoft SQL Server 2012 - Books Online

• ALTER TABLE Statement
• DROP TABLE Statement

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - October 2012.