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

IntroductionHere 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."?


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.


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. 
Previous
Next Post »

2 comments

Click here for comments
Anonymous
admin
August 24, 2016 ×

Simple and very nice explanation. I like it. Keep it up.

Reply
avatar
September 09, 2016 ×

Thanks for your valuable feedback..stay connected and keep reading..

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..