数据库开发——参照完整性——在外键中使用Delete on cascade选项(转载)

参照完整性在设计数据库时需要重视,在我作为DBA的生涯中,看到很多设计走了极端的路子。

在进入DELETE CASCADE选项的详细说明前,先来看看另外一个选项,可以在具有外键的表中设置UDPATE CASCADE选项。在我的工作生涯中,我从来没有遇到过必须通过外键来更新一列或多列。

创建实例表:

在本例中,创建两个表,并用外键关联起来。主表有99999行记录,子表对于每条父记录,有19条记录。下面是创建语句:

-- Table creation logic

--parent table

CREATE TABLE [dbo].[Order](

 [OrderID] [bigint] NOT NULL,

 [OrderData] [varchar](10) NOT NULL,

 CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED

    ([OrderID] ASC)

)

GO

-- child table

CREATE TABLE [dbo].[OrderDetail](

 [OrderDetailID] [bigint] NOT NULL,

 [OrderID] [bigint] NULL,

 [OrderData] [varchar](10) NULL,

 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED

    ([OrderDetailID] ASC)

)

GO

-- foreign key constraint

ALTER TABLE [dbo].[OrderDetail]  WITH CHECK

ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])

REFERENCES [dbo].[Order]([OrderID])

ON DELETE CASCADE

GO

-- data load

DECLARE @val BIGINT

DECLARE @val2 BIGINT

SELECT @val=1

WHILE @val < 100000

BEGIN 

   INSERT INTO dbo.[Order] VALUES(@val,'TEST' + CAST(@val AS VARCHAR))

  

   SELECT @val2=1

   WHILE @val2 < 20

   BEGIN 

      INSERT INTO dbo.[OrderDetail] VALUES ((@val*100000)+@val2,@val,'TEST' + CAST(@val AS VARCHAR))

      SELECT @val2=@val2+1

   END

   SELECT @val=@val+1

    

END

GO

第一个例子:

现在先让我们从[Order]表中移除一条数据,注意,我在每个查询中使用了DBCC DROPCLEANBUFFERS,来确保缓存中没有数据:

DBCC DROPCLEANBUFFERS

GO

DELETE FROM [Order] WHERE OrderID=24433

GO

在运行上面语句之后,可以查询[OrderDetail]表来确认记录是否已经被移除。这是为了了解,我们没有使用DELETE CASCADE选项时,要做什么操作,来确保数据移除,并看到他们的结果:

SELECT * FROM orderdetail WHERE orderid=24433

执行以后可以发现是没有数据的。下面再执行一下语句:

ALTER TABLE [dbo].[OrderDetail] DROP CONSTRAINT [FK_OrderDetail_Order]

GO

ALTER TABLE [dbo].[OrderDetail]  WITH CHECK

ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])

REFERENCES [dbo].[Order]([OrderID])

GO

现在让我们运行一下脚本,记住当有DELETECASCADE选项时,我们必须先从[OrderDetail]。中删除记录,想象一下,当我们有5、6个表对一个父表具有外键关联时,删除数据将要单独对每个表进行删除后才能删除父表。

DBCC DROPCLEANBUFFERS

GO

DELETE FROM [OrderDetail] WHERE OrderID=24032

DELETE FROM [Order] WHERE OrderID=24032

GO

我们可以通过SQL Profiler来监控两个处理方法的性能。你可以看到觉有DELETE CASCADE选项的处理占用的资源更少:

DELETE CASCADE

CPU (ms)

Reads

Writes

Duration

Yes

281

12323

2

950

No

374

24909

3

1162

 

第二个例子:

其中一个SQLServer最佳实践是在外键列并经常在where子句、join表中出现的字段,加上索引,现在我们对[OrderDetail]表加上索引,然后运行上面的查询,首先先加索引:

CREATE NONCLUSTERED INDEX IX_OrderDetail_OrderID ON dbo.[OrderDetail](OrderID)

GO

接下来,运行改动后的执行,并监控性能:

DBCC DROPCLEANBUFFERS

GO

DELETE FROM [OrderDetail] WHERE OrderID=90032

DELETE FROM [Order] WHERE OrderID=90032

GO

ALTER TABLE [dbo].[OrderDetail]  WITH CHECK

ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])

REFERENCES [dbo].[Order]([OrderID])

ON DELETE CASCADE

GO

DBCC DROPCLEANBUFFERS

GO

DELETE FROM [Order] WHERE OrderID=90433

GO

从下面的结果可以看到,使用DELETECASCADE选项在多表删除时,性能更好,并且能自动清除子表数据:

DELETE CASCADE

CPU (ms)

Reads

Writes

Duration

Yes

0

300

7

79

No

0

312

6

64

posted @   qanholas  阅读(436)  评论(0编辑  收藏  举报
编辑推荐:
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
阅读排行:
· 周边上新:园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源!
· .NET周刊【3月第1期 2025-03-02】
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
历史上的今天:
2011-12-05 使用NEWSEQUENTIALID解决GUID聚集索引问题
点击右上角即可分享
微信分享提示