Sql Server: Drop or Truncate Parent Table by Dropping All Foreign Key Constraints

Introduction: Here in this article you will learn the following:

1. How to find all foreign key constraints names and child tables referring to parent table?
2. How to forcefully drop or truncate parent table?
3. How to resolve the sql error "Could not drop object 'tablename' because it is referenced by a FOREIGN KEY constraint."?
4. How to resolve the sql error "Cannot truncate table 'tablename' because it is being referenced by a FOREIGN KEY constraint."?

In previous articles i explained How to Find all primary and foreign key constraints on each or any table in database and Search any text in all stored procedures, views and functions and Concatenate rows values as a comma separated string using for xml path and stuff in sql server and Try catch to handle exception and commit/rollback transaction in sql server and Difference between primary key and foreign key in sql server

Implementation: Let’s create a parent table and two child tables referring to parent table

GO

-- Create a parent table using following script

CREATE TABLE tbEmployee

(

            EmployeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

            EmployeeName VARCHAR(100)

);

GO

-- Create a child table that refer above parent table 'tbEmployee' using following script

CREATE TABLE tbEmployeeExperience

(

            CompanyName VARCHAR(200),

            TotalMonths INT,

            EmployeeId INT FOREIGN KEY REFERENCES tbEmployee(EmployeeId),

);

GO

-- Create another child table that refer above parent table 'tbEmployee' using following script

CREATE TABLE tbEmployeQualification

(

            QualificationName VARCHAR(200),

            EmployeeId INT FOREIGN KEY REFERENCES tbEmployee(EmployeeId),

);

--Now try to drop parent table 'tbEmployee'

DROP TABLE tbEmployee 

--You will get the error:

Msg 3726, Level 16, State 1, Line 1

Could not drop object 'tbEmployee' because it is referenced by a FOREIGN KEY constraint.

--Now try to truncate parent table 'tbEmployee'

TRUNCATE TABLE tbEmployee

--You will get the error:

Msg 4712, Level 16, State 1, Line 1

Cannot truncate table 'tbEmployee' because it is being referenced by a FOREIGN KEY constraint.

The reason of these two errors is that parent table’s primary key “EmployeeId” is being used as foreign key in child tables. So it is clear that parent table can neither be dropped nor truncated if it is referred by some child tables. So the question arise how to forcefully drop or truncate that table in such cases?

The answer is either drop all child tables referring parent table before dropping parent table or remove all foreign key constraints referring parent table.

To check all foreign key constraint names and the referring tables execute the following query

Find all foreign keys names and tables referring to our parent table 'tbemployee'

SELECT name AS 'Foreign Key Constraint',

OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'

FROM sys.foreign_keys

WHERE referenced_object_id = OBJECT_ID('tbEmployee')

Result:

Foreign Key Constraint	Child Table
FK__tbEmploye__Emplo__17036CC0	dbo.tbEmployeeExperience
FK__tbEmploye__Emplo__18EBB532	dbo.tbEmployeQualification

Once detected we need to delete all foreign key constraint one by one by their name using the following statement

ALTER TABLE dbo.tbEmployeeExperience DROP CONSTRAINT FK__tbEmploye__Emplo__17036CC0;

ALTER TABLE dbo.tbEmployeQualification DROP CONSTRAINT FK__tbEmploye__Emplo__18EBB532;

Once all foreign key constraints are deleted parent table can be dropped or truncated.

But what if there are many foreign key constraints referencing to the parent table

Then deleting all constraints by their name one by one will be time consuming.

In that case you can execute the following script (dynamic sql) to automatically drop all foreign key constraints referring to parent table.

Transact-SQL Dynamic Script to Automatically Find and Remove Foreign Key Constraints

DECLARE @TableName VARCHAR(100)='tbEmployee'

DECLARE @SQL VARCHAR(MAX)=''

SELECT @SQL = @SQL + 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) +

' DROP CONSTRAINT ' + name + CHAR(13)

FROM sys.foreign_keys

WHERE referenced_object_id = OBJECT_ID(@TableName)

-- (OPTIONAL)TO CHECK DYNAMICALLY CREATED DROP CONSTRINT STATEMENTS

PRINT @SQL

--EXEXUTE THE SCRIPT TO DROP ALL FOREIGN KEY CONSTRAINTS

EXEC (@SQL)

--NOW WE CAN DROP OR TRUNCATE PARENT TABLE 'tbEmployee'

DROP TABLE tbEmployee

Explanation: It actually automatically creates as many as drop constraint queries as there are constraints referring to parent table. Then on executing this dynamic sql script it drops all the constraints and you will be able to drop or truncate the parent table

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post