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|
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|