数据库开发——参照完整性——在外键中使用Delete on cascade选项
来源:互联网 发布:excel中工龄的算法 编辑:程序博客网 时间:2024/06/17 21:21
原文:
http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012731
参照完整性在设计数据库时需要重视,在我作为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] FOREIGNKEY([OrderID])
REFERENCES [dbo].[Order]([OrderID])
ON DELETECASCADE
GO
-- data load
DECLARE @val BIGINT
DECLARE @val2 BIGINT
SELECT @val=1
WHILE @val< 100000
BEGIN
INSERT INTO dbo.[Order]VALUES(@val,'TEST'+ CAST(@valAS VARCHAR))
SELECT @val2=1
WHILE @val2 < 20
BEGIN
INSERT INTO dbo.[OrderDetail]VALUES ((@val*100000)+@val2,@val,'TEST'+ CAST(@valAS 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] FOREIGNKEY([OrderID])
REFERENCES [dbo].[Order]([OrderID])
GO
现在让我们运行一下脚本,记住当有DELETECASCADE选项时,我们必须先从[OrderDetail]。中删除记录,想象一下,当我们有5、6个表对一个父表具有外键关联时,删除数据将要单独对每个表进行删除后才能删除父表。
DBCC DROPCLEANBUFFERS
GO
DELETE FROM[OrderDetail] WHEREOrderID=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 NONCLUSTEREDINDEX IX_OrderDetail_OrderIDON dbo.[OrderDetail](OrderID)
GO
接下来,运行改动后的执行,并监控性能:
DBCC DROPCLEANBUFFERS
GO
DELETE FROM[OrderDetail] WHEREOrderID=90032
DELETE FROM[Order] WHERE OrderID=90032
GO
ALTER TABLE[dbo].[OrderDetail] WITH CHECK
ADD CONSTRAINT[FK_OrderDetail_Order] FOREIGNKEY([OrderID])
REFERENCES [dbo].[Order]([OrderID])
ON DELETECASCADE
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
- 数据库开发——参照完整性——在外键中使用Delete on cascade选项
- T-SQL级联删除——ON DELETE CASCADE
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- 您想知道如何在Access 2003中使用ON UPDATE CASCADE/ON DELETE CASCADE,ON UPDATE SET NULL/ ON DELETE SET NULL吗?
- 实训数据库开发应用——数据完整性
- 级联引用完整性约束ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
- 数据库—SQL—数据库完整性
- 数据库复习3——数据库完整性
- ON DELETE CASCADE ON UPDATE CASCADE
- ON UPDATE CASCADE ON DELETE CASCADE
- 数据库级联更新 on update cascade和级联删除 on delete cascade
- MySQL开发中的外键与参照完整性
- Mysql开发中的外键与参照完整性
- Mysql开发中的外键与参照完整性
- 用友U8分页存储过程
- VMware使用总结
- poj2187 凸包+graham+旋转卡壳+最远点对
- 自定义分页存储过程 及VB.NET 调用
- 遍历所有窗体控件
- 数据库开发——参照完整性——在外键中使用Delete on cascade选项
- 正则表达式语法
- 手动建立主从表关系数据库显示
- 手动初始化dataset并添加表到其中
- 自定义DataAdapter批量更新
- 判断一个数是否是平方数
- 关于PostMessage在多线程中的内存释放问题
- DateTime赋空值
- Ubuntu 12.04下为火狐浏览器安装flash插件