How to truncate table which is referenced by a foreign key in SQL Server
--------How truncate table referenced by foreign key-------------
USE SampleDB
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)
truncate table dbo.customer
----Resolution----
--Know parent table and reference table
SELECT Schema_Name(Schema_id) as TableSchemaName,
object_name(FK.parent_object_id) ParentTableName,
object_name(FK.referenced_object_id) ReferenceTableName
FROM sys.foreign_keys AS FK
WHERE object_name(FK.referenced_object_id)='customer'
and Schema_Name(Schema_id)='dbo'
--Backup Foreign key
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [Fk_CustomerId] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Customerid])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [Fk_CustomerId]
GO
--Find which foreign key is conflicting
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')
--Drop the foreign key
ALTER TABLE dbo.[Orders] DROP CONSTRAINT Fk_CustomerId
--truncate table
truncate table dbo.customer
--Restore deleted foreign key
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [Fk_CustomerId] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Customerid])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [Fk_CustomerId]
GO