原文:
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] 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
|
分享到:
相关推荐
(本次实验难点是完整性约束,特别是参照完整性约束(主外键)!应注意对完整性约束作验证,关键给出违反约束的验证,要说明理由)
SQL数据完整性 主键_外键_unique_check_primary
使用eclipse获取mysql数据库中的表结构以及主外键
(1)分别定义ScoreDB数据库中各基表的主键、外键,实现实体完整性约束和参照完整性约束; (2)往student表插入一条跟已有的学号相同的学生数据,验证实体完整性约束; (3)向score表中插入一条数据,其中数据中的...
本文介绍在在MySQL数据库中定义数据表、设置索引、定义外键、级联等操作。
SQL Server 数据完整性介绍,主键和外键介绍,数据类型介绍
数据库完整性详解,十分详细,附有详细上机图以及步骤。
1. 通过对完整性规则的定义实现,熟悉了解SQL Server数据库中实体完整性、参照完整性、断言等完整性保证的规则和实现方法,加深对数据完整性的理解。 2. 通过对安全性相关内容的定义,熟悉了解SQL Server数据库中...
数据库的安全性实验,在SQL Server企业管理器中,设置SQL Server的安全认证模式,实现对SQL Server的用户和角色管理,设置和管理数据操作权限。 具体内容如下: 设置SQL Server的安全认证模式(Windows或SQL ...
本文介绍了在MySQL数据库中定义外键的方法。
在比较复杂的数据库中,经常要处理表中的外键关系,本人写了一些处理外键的方法,还忘诸位多多指点……
内包含外键约束模式,数据库的视图基本操作
达梦数据库
Mysql数据库如何删除某一个数据库的所有外键并不删除相应的索引,一个sql脚本解决,无需知道外键的名称。
数据库主外键详解,带代码片段。让你直观了解数据库的主外键设置
数据库主键和外键设计的原则
数据库应用系统设计与实现 实验类型:设计型 实验地点:2楼312 实验时间:12月14日、21日周五1-3/5-7节 实验内容: 系统概述: 某银行需要开发ATM存取款机系统实现如下功能: 1)开户(到银行填写开户申请单,卡号...
外键——朱腾.sql
数据库完整性与安全性试验,完整性涉及主键、外键、候选键约束,安全性涉及check、trigger
我们说这是维持关联表的参照完整性。 在图形化界面中,在 外键 选项卡,只需简单地点击外键栏位来编辑。使用外键的工具栏,能让你创建新的、编辑或删除选择的外键栏位。 添加外键:添加一个外键到表。 删除外键:...