Cannot truncate table because it is being referenced by a FOREIGN KEY constraint - SQL Server / TSQL Tutorial Part 70

Scenario: 

You need to truncate a table but when you try to execute truncate table tableName. You get below error.

Msg 4712, Level 16, State 1, Line 43
Cannot truncate table 'SchemaName.TableName' because it is being referenced by a FOREIGN KEY constraint.

How would you truncate this table?

Solution:

As the table in involved in Foreign Key relationship, you need to drop the foreign key constraint first and then execute the truncate table statement.

Let's demo this example, I am going to create two table dbo.Customer and dbo.Orders and then create Foreign Key Constraint on one of the column of Dbo.Orders to dbo.Customer table.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int
    )

--Create Foreign Key Constraint 
    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

Now if I try to truncate dbo.Orders table, it will throw no error. But when I try to truncate dbo.Customer table it will throw error as dbo.Customer is begin references by Foreign Key Constraint.

How to truncate a table which is reference by Foreign Key Constraint in SQL Server

We can drop the constraint for now, truncate the table and then recreate Foreign key constraint.

To find the Foreign Key Constraints on a table, you can use below statement.

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Customer')

How to get Constraint name from system tables in SQL Server

The below script can be used to generate drop Constraint statement for your table

SELECT 
    'ALTER TABLE ' 
    +  OBJECT_SCHEMA_NAME(parent_object_id) 
    +'.[' + OBJECT_NAME(parent_object_id) 
    +'] DROP CONSTRAINT ' 
    + name as DropFKConstraint
FROM sys.foreign_keys

WHERE referenced_object_id = object_id('dbo.Customer') 

How to drop Foreign Key Constraint on Table in SQL Server

Take the result for Drop Foreign Key Constraint and execute, After that run your truncate table statement to truncate table. It should complete without any error.

Video Demo : Cannot truncate table because it is being referenced by a FOREIGN KEY constraint