filmov
tv
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)
-----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
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
Комментарии