Transcript 第五章数据库完整性

第五章 数据库完整性
数据库完整性
 数据是数据库的核心，数据的正确和完整与否将直接影响数据
库的使用。
 数据的完整性和安全性是两个不同概念；
 数据的安全性
 保护数据库防止恶意的破坏和非法的存取
 防范对象：非法用户和非法操作
 数据的完整性
 防止数据库中存在不符合语义的数据，也就是防止数据库中存
在不正确的数据
 防范对象：不合语义的、不正确的数据
本次课内容
一． 数据完整性概述
二． 在SQL Server 2005中实现数据完整性
三． 其他相关知识

触发器
一、数据库完整性概述
 1、DBMS必须能够提供
 提供定义完整性约束条件的机制
• 完整性约束条件 = 完整性规则，由SQL的ddl语句来实现；
 提供完整性检查的机制
• 检查数据是否满足完整性约束条件的机制；
 违约处理
• 若用户操作违反完整性约束条件，采取一定的动作，如拒
绝执行（NO ACTION）、级联执行或其他操作；
 2、数据库完整性约束机制分类
完整性约束条件作用的对象有关系、元组、列三种；可分为：
 实体完整性：实体完整性是为了保证表中的数据唯一；
 参照完整性：参照完整性用于确保相关联的表间的数据应保持一致，
避免因一个表的记录修改，造成另一个表的内容变为无效的值．一
般来说，参照完整性是通过外键和主键来维护的；
 域完整性：域完整性可以保证数据的取值在有效的范围内；
 用户定义完整性：由用户自行定义的，不同于前面３种的完整性，
也可以说一种强制数据定义；
1）实体完整性
 实体（Entity）：是数据逻辑模型中的一个概念。现实世界是一
组实体的基本对象和这些对象的联系（Relation）构成的。在
数据库中，我们可以理解一条记录就是一个实体。
 实体完整性：在现实世界中，任何一个实体都有区别于其他实
体的特征，即在数据库中，所有的记录都应该有惟一的标识，
这就是实体完整性的含义（规则2.1：实体完整性规则）。
 常见的实体完整性机制包括：
 主键（Primary Key）；惟一码（Unique）；
2）参照完整性
 参照完整性是指在两个表的主键和外键之间数据的完整性，其
含义包括：
 参照完整性保证被参照表和参照表之间数据的一致性；
 可以防止数据丢失或者无意义的数据；
 可以禁止在从表中插入被参照表中不存在的关键字的记录。
 参照完整性的常见实现机制包括：
 外键（Foreign Key）、检查（Check）、触发器（Trigger）；存
储过程（Stored Procedure）
 在输入或删除记录时，可以用来保持所有表之间定义的关
系，以确保键值在所有表中一致。
 关系模型的参照完整性定义
 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为
外码
 用REFERENCES短语指明这些外码参照哪些表的主码
3）域完整性
 域：指列（字段），所以域完整性就是指列的完整性；
 域完整性要求列（字段）的数据具有正确的数据类型、格式和
有效的数值范围。
 域完整性的常见实现机制包括：




默认值（Default）
检查（Check）
数据类型（Data type）
规则（Rule）
4）用户自定义完整性
 用户定义的完整性就是针对某一具体应用的数据必须满
足的语义要求；可直接由RDBMS提供，而不必由应用程
序承担；系统将实现数据完整性的要求直接定义在表上
或列上。
 常见的实现机制包括：
 规则（Rule）、创建数据表时的所有约束（Constraint）、触发
器（Trigger）、存储过程（Stored Produre）；
二、在SQL Server 2005中实现数据完整性
 SQL Server 2005提供了完善的数据完整性机制，主
要包括约束、默认值和规则3类；
 创建及管理约束及约束对象
 默认值约束及默认值对象
 创建及管理规则对象
1、创建及管理约束及约束对象
 主键Primary key约束
 惟一值Unique约束
 外键Foreign key约束
 检查Check约束
1）创建及删除主键约束及惟一值约束
单属性构成的码的两种说明方法 ：



定义为列级约束条件，定义为表级约束条件
多个属性构成的码只有一种说明方法：

定义为表级约束条件
创建约束方式：
I.
创建表时同时创建primary key约束或unique约束
II.
修改表时同时创建primary key约束或unique约束
i）创建表时同时创建primary key约束或unique约束
 语法结构：
CREATE TABLE table_name
(column_name data_type (NULL| NOT NULL)
[ [CONSTRAINT constraint_name]
{ PRIMARY KEY | unique}
[, ...n]
例1：定义数据表student时，为学号sno字段创建主键约束，对
姓名sname字段定义惟一值约束；
方法1：在列级定义主码
Use stu
CREATE TABLE student
（Sno char（9） constraint sno_pk primary key,
Sname char(8) constraint sname_uk unique,
Ssex char(2) ，
Sage smallint ，
Sdept varchar(20)）
方法2：在表级定义主码
CREATE TABLE Student
(Sno CHAR(9) ，
Sname CHAR(20) ，
Ssex CHAR(2) ，
Sage SMALLINT，
Sdept CHAR(20)，
PRIMARY KEY (Sno) )
实体完整性检查：
 插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自
动进行检查。包括：
 1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改
 2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插
入或修改
检查方法：全表扫描、索引
ii）通过修改表时同时创建primary key约束或unique约束
 语法格式：
ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
primary key | unique
(column [,...n])
例1：对数据表student学号sno字段添加主键约束，对学生姓名
添加惟一值约束；
Alter table student
Add constraint sno_pk primary key sno
Alter table student
Add constraint sname_uk unique sname
删除primary key约束或unique约束
 语法格式：
ALTER TABLE table_name
DROP CONSTRAINT constraint_name [, ...n]
例1：删除student中的主键约束和惟一值约束
ALTER TABLE student
Drop constraint sno_pk，sname_uk
 查看主键约束的定义文本
EXEC sp_helptext <constraint_name>
 查看表中主键约束
EXEC sp_helpconstraint <table_name>
 查看数据库中所有主键约束
SELECT name from sysobjects WHERE type=‘K‘
备注： K：primary key或unique约束；F：foreign key
C：check约束；D：默认值；R：规则；TR：触发器；
U：用户表； V：视图
2）创建及管理外键约束
 对于两个相关联的表（主表与从表）进行数据插入和删除时，
通过参照完整性保证他们之间数据的一致性。
 利用Foreign Key定义从表的外码，Primary Key或者Unique定
义主表中的主码或惟一码（不允许为空），可实现主表与从表
之间的参照完整性。
 定义表间参照关系：先定义主表主码（或惟一码），再对从表
定义外码约束。
可能破坏参照完整性的情况及违约处理
被参照表（例如Student）
参照表（例如SC）
违约处理
可能破坏参照完整性
插入元组
拒绝
可能破坏参照完整性
修改外码值
拒绝
删除元组
修改主码值
可能破坏参照完整性
可能破坏参照完整性
拒绝/级连删除/设置为空值
拒绝/级连修改/设置为空值
 当主码外码发生不一致时，系统可采取以下策略：
 1. 拒绝(NO ACTION)执行
• 默认策略
 2. 级联(CASCADE)操作
 3. 设置为空值（SET-NULL）
• 对于参照完整性，除了应该定义外码，还应定义外码列
是否允许空值
i）创建表时同时定义Foreign key约束
 语法格式：
CREATE TABLE table_name
(column_name data_type [FOREIGN KEY]
REFERNCES ref_table(ref_column)
[ on delete {CASCADE | no action}]
--cascade表示主从表删除时一致，进行级联删除
--no action 表示回滚主表的删除操作,为默认设置
[ on update {CASCADE | no action}]
--主从表更新时一致
[, ...n] )
--n表示可定义多个字段
例1：在数据库stu中创建主表student和course，主键分别为
sno和cno，创建从表sc，sc.sno为外码，与数据表student主
键对应，当对主表进行更新或删除操作时，对从表采用级联操
作；sc.cno为外码，与数据表course主键对应，当对主表进行
更新或删除操作时，对从表采用NO Action方式。
Use stu
Create table student（sno char(9) primary key，sname……）
Create table course （cno char(4) primary key，sname……）
Create table sc
（sno char(9) foreign key references student(sno)
on delete cascade on update cascade，
cno char(4) foreign key references course(cno)
on delete no action on update no action ，
Grade smallint ）
ii）通过修改表定义Foreign key约束
 语法格式：
ALTER TABLE table_name
ADD [constraint constraint_name]
[FOREIGN KEY] (column[,…n])
REFERNCES ref_table(ref_column [,…n])
[ on delete {CASCADE | no action}]
[ on update {CASCADE | no action}]
例1：假设数据库stu中student和course为主表，主键分别为sno
和cno， sc为从表，将sc.sno定义为外码。
Use stu
Alter table sc
ADD constraint sc_fk
foreign key sno references student(sno)
 iii）删除表间的参照关系
删除表间的参照关系，删除从表的外码约束即可；
语法格式：
Alter table table_name
Drop constraint constraint_name [,…n]
iv）查看外键约束
sp_helptext 、sp_helpconstraint、
select name from sysobjects where type=‘F’
3）创建及删除检查约束
 对插入列中的值进行限制，以强制域的完整性；
 该约束在执行INSERT语句或UPDATE时起作用；
 使用该约束可实现当用户在向表中插入或更新数据时，由SQL
Server检查新行中的带有该约束的列值使其必须满足约束条件。
 CHECK约束作用域
 单列：列级CHECK约束
 多列：表级CHECK约束
i）创建数据表时创建检查check约束
语法格式：
CREATE TABLE table_name
(column_name data_type (NULL|NOT NULL)
[ [CONSTRAINT check_name]
CHECK (logical_expression)]
[，…n ]）
例1：定义student表，并限定ssex只允许取‘男’或‘女’；
CREATE TABLE student
（ Sno char(9) Primary Key,
Sname char(8),
Ssex char(2) check （ssex in （‘男’，‘女’）） ，
Sage smallint ，
Sdept varchar(20)）
 例2：在定义Student中，要求男生的年龄在15～25岁之间，女生的
年龄在15—24岁之间；
CREATE TABLE Student
(Sno char(9) Primary Key,
Sname char(8),
ssex char(2),
Sage smallint,
Sdept varchar(20),
CHECK(SAGE>=15 AND
( (SSEX='男' AND SAGE<=25) OR (SSEX='女' AND
SAGE<=24) )))
ii）修改数据表时添加检查check约束
语法格式：
ALTER TABLE table_name
ADD [CONSTRAINT check_name]
CHECK (logical_expression)
-- check约束名称
--check约束表达式
例1：在表student的列sage上添加一个CHECK约束，以保证输
入的数据大于0并且小于100；
Alter table student
Add CONSTRAINT sage_ck
Check （sage>=0 and sage<=100)
例2：在表student的列sno上添加一个CHECK约束，要求学号必
须由9个数字字符组成，并且不等于0000000000.
Alter table student
Add CONSTRAINT sno_ck
Check （sno like ‘[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]’ and
sno<>’000000000’)
iii）查看约束的定义信息
 查看约束的定义文本
EXEC sp_helptext <constraint_name>
 查看表中约束
EXEC sp_helpconstraint <table_name>
 查看数据库中所有约束
SELECT name from sysobjects WHERE type=‘C‘
例1：查看表student上约束sno_ck的定义文本
USE stu
EXEC sp_helptext sno_ck
Go
例2：查看数据库stu中所有的check约束
USE stu
SELECT name from sysobjects WHERE type=‘C‘
iv）删除检查check约束
语法格式：
ALTER TABLE table_name
DROP CONSTRAINT check_name
例1：删除数据表student中sno字段的check约束
ALTER TABLE student
DROP CONSTRAINT sno_ck
2、默认值约束
 默认值约束的作用：就是当用户没有为定义为default约束的字
段输入数据时, 由default 约束提供默认的数据。
 默认值对象：作为一种单独的数据库对象，它是独立于表的，
删除表并不能删除默认值，而是需要使用DROP DEFAULT命令
才能删除。
 对于某些字段，可在程序中指定默认值，以方便用户，一个字
段默认值的建立可通过如下两种方式实现：
 在定义表或修改表时，定义默认值约束；
 先定义默认值对象，然后将对象绑定到表的相应字段；
1）在表中定义及删除默认值约束
 默认值约束的定义
 1）定义表时定义字段的默认值约束
 2）修改表时添加一个字段的同时定义相应约束
 3）对表中指定的列定义默认值
 默认值约束的删除
i）定义表时定义字段的默认值约束
 定义默认值约束语法格式：
Create table table_name
( column_name datatype not null / null
[ Constraint constraint_name ]
[ DEFAULT (constraint_expression)]
[,…n]
--参数n表示可定义多个数据字段
)
--定义列名、数据类型、是否空值及默认值约束
例1：定义数据表student中字段ssex的默认值为‘女’；
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY，
Sname CHAR(8) NOT NULL，
Ssex CHAR(2) default (‘女’) ，
Sage SMALLINT，
Sdept CHAR(20) )
ii）修改表时添加一个字段的同时定义相应约束
 修改默认值约束语法格式：
Alter table table_name
( ADD column column_name datatype not null | null
[Constraint constraint_name]
--指定约束名
[ DEFAULT (constraint_expression)] with values --默认值约束表达式
--with values 仅用于在对表添加新字段的情况下
)
例2：在数据表student中添加字段入学日期ruxue_date，默
认值为当前日期；
Alter table student
Add ruxue_date smalldatetime null
Constraint date
Default getdate() with values
iii）对表中指定的列定义默认值约束
 语法格式：
Alter table table_name
( ADD Constraint constraint_name
--指定约束名
[ DEFAULT constraint_expression]
--默认值约束表达式
For column
)
例3：为数据表sc中字段grade添加默认值为0；
Alter table sc
Add Constraint grade_default
Default 0 For grade
iv）默认值约束的删除
 语法格式：
Alter table table_name
DROP Constraint constraint_name
例1：删除表sc中字段grade的默认值；
Alter table sc
DROP Constraint grade_default
--删除指定约束名
三、其他知识
 规则
 触发器
1、创建及管理规则对象
 规则：对录入数据列中的数据所实施的完整性约束条件，它指
定了插入到数据列中的可能值。
 规则是SQL Server 2005数据库中独立于表和视图的数据对象，
与其作用的表是独立的，删除表时不会删除规则。
 规则就相当于数据表的数据验收员，当插入或修改的数据放入
数据表时，它就检查数据是否符合要求，符合要求的放入数据
表，不符合的拒之门外。这样就保证了放入数据表的数据是正
确的。
 如果列同时有与之相关联的默认值和规则，则默认值必须在规
则定义的范围内。与规则冲突的默认值永远不能被插入。
1）定义规则对象
 规则对象的使用方法与默认值对象使用步骤类型；
 定义规则对象
 将规则对象绑定到列或用户自定义类型
 定义规则对象的语法结构
CREATE RULE rule AS condition_expression
 将规则对象绑定到自定义类型或列 语法格式：
sp_bindrule [ @rulename = ] 'rule' ,
[ @objname = ] 'object_name'
[ , [ @futureonly = ] 'futureonly_flag' ]
例1：创建一个规则，用于指定性别为‘男’或者‘女’，并绑定到数据
表student的ssex字段。
CREATE RULE ssex_rule
AS @sex =‘男’ or @sex =‘女’
EXEC sp_bindrule ‘ssex_rule‘ , ' student.ssex‘
例2：创建一个规则，用于指定学号首位以1-9开头，其他位以0-9
结尾，并绑定到数据表student的sno字段。
CREATE RULE sno_rule
AS @num like '[1-9][0-9][0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]'
EXEC sp_bindrule ‘sno_rule‘ , ' student.sno‘
2）删除规则对象
 删除规则对象之前，首先应使用系统存储过程sp_unbindrule解
除被绑定对象与规则对象之间的绑定关系。
解除规则绑定语法格式：
sp_unbindrule [@objname =] 'object_name'
[, [@futureonly =] 'futureonly_flag']
删除规则语法格式：
DROP rule {rule} [, …n]
例1：解除规则对象sdept_rule与数据表student的sdept字段的绑
定，然后删除规则对象sdept_rule;
Use stu
If exists (select name from sysobjects where name =
‘sdept_rule’ and type=‘R’）
Begin
Exec sp_unbindrule ‘student.sdept’
Drop rule sdept_rule
End
 对比：
 约束:定义表中有效的数据，在事务处理前执行，性能较好；
 默认和规则:提供独立的可以与多个表联系的对象实现数据
完整性，在事务处理前执行
2、触发器
 触发器是一种特殊类型的存储过程，只要对它所保护的数
据进行修改，它就会自动触发。触发器主要是通过事件进
行触发而被执行的，而存储过程可以通过存储过程名称而
被直接调用。
 一般情况下，对表数据的操作有插入、修改和删除，对数
据的更新、插入及删除被视为数据修改事件。因而维护数
据的触发器也可分为3种类型：insert,update和delete。
一个触发器由三部分组成：
 事件。事件是指对数据库的插入、删除修改等操作。触发
器在这些事件发生时将开始工作。
 条件。触发器将测试条件是否成立。如果条件成立，就执
行相应的动作，否则什么也不作。
 动作。如果触发器测试满足预定的条件，那么就由DBMS
执行这些动作（即对数据库的操作）。
1）定义触发器
 语法格式：
CREATE TRIGGER <触发器名> ON <表名/视图名>
{FOR | AFTER | INSTEAD OF} <触发事件>
AS <触发动作体>
-- AFTER用于说明触发器在指定操作都成功执行后触发，是默认设置；
-- INSTEAD OF指定用触发器中的操作代替触发语句的操作；
-- <触发事件> INSERT、DELETE、UPDATE；
 例1：对于数据库stu，创建一个触发器update_student，当用
户试图向student表中添加或修改数据时，该触发器向客户端
显示一条“插入或更新了数据表xs！”的消息;
USE stu
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'update_student' AND type = 'TR')
DROP TRIGGER update_student
Create trigger update_student ON student
FOR INSERT, UPDATE
AS PRINT '插入或更新了数据表student！‘
测试：向student表插入一条记录
结果：客户端显示'插入或更新了数据表student！‘
 例2：对于数据库stu，创建一个触发器，不允许对student删
除或修改记录时，同时客户端显示‘不允许增加或修改记录’。
CREATE TRIGGER student_tr1 ON student
INSTEAD OF DELETE, UPDATE
AS
PRINT ‘不允许增加或修改记录'
测试：delete from student where sno='200815001'
执行结果：
说明： 在表或视图上，每个 INSERT、UPDATE 或 DELETE 语
句最多可以定义一个 INSTEAD OF 触发器。
触发器中使用的特殊表
 inserted 逻辑表：当向表中插入数据时，INSERT触发器触发执
行，新的记录插入到触发器表和inserted表中。
 deleted逻辑表：用于保存已从表中删除的记录，当触发一个
DELETE触发器时，被删除的记录存放到deleted逻辑表中。
 Inserted 和 deleted 总是在内存中而不是在磁盘上，因为它们
是逻辑表，而且通常很小。若要检索deleted、 inserted 表中所
有记录，可是用语句：select * from inserted [deleted]
 例3：对于数据库stu中的数据表sc，创建一个触发器，插
入记录时，当字段grade小于60时，自动改为0；
CREATE TRIGGER sc_grade ON sc
FOR insert
AS
begin
update sc set grade =0
where grade<60 and sno in
(SELECT inserted.sno FROM inserted)
end
2）修改触发器
 语法格式：
Alter trigger trigger_name on ( table | view )
{for { [delete] [,] [insert] [,] [update] }
As sql_statement [ …n ]
}
3）删除触发器
 语法格式：
DROP trigger trigger_name [,…n]
小结
一． 数据完整性概述

实体完整性；参照完整性

域完整性；用户自定义完整性
二． 在SQL Server 2005中实现数据完整性


创建和管理约束
•
Primary key约束；Unique约束；
•
Foreign key约束；
•
Check约束；
创建和管理默认值约束
三．扩充知识（规则、触发器）