How to truncate table which is referenced by a foreign key in SQL Server

preview_player
Показать описание
---------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)

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

--Drop the foreign key
ALTER TABLE dbo.[Orders] DROP CONSTRAINT Fk_CustomerId

--truncate table

--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
Рекомендации по теме
Комментарии
Автор

Good explanation of the concept with a clear demo of how to resolve . thanks for sharing !

rvee
visit shbcf.ru