上机实验5 数据完整性管理2008

上机实验5 数据完整性管理

5.1 实验目的

1、理解数据库完整性约束的概念和原理；

2、掌握声明型数据完整性和过程型数据完整性的实现方法；

3、通过练习正确理解触发器的作用，类别，如何产生作用；

4、通过练习熟悉创建触发器的语句。

5.2 实验练习预备知识点

5.2.1 完整性的概念

数据完整性（Data Integrity）是指数据的精确性（Accuracy ） 和可靠性（Reliability ）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

5.2.2 完整性的类型

1 实体完整性（Entity Integrity）

实体完整性规定表的每一行在表中是惟一的实体。表中定义的

UNIQUE PRIMARYKEY 和IDENTITY 约束就是实体完整性的体现。

2 域完整性（Domain Integrity）

域完整性是指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。表中的CHECK 、FOREIGN KEY 约束和DEFAULT 、 NOT NULL定义都属于域完整性的范畴。

3 参照完整性（Referential Integrity）

参照完整性是指两个表的主关键字和外关键字的数据应对应一致。它确保了有主关键字的表中对应其它表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。参照完整性是建立在外关键字和主关键字之间或外关键字和惟一性关键字之间的关系上的。在SQL Server 中，参照完整性作用表现在如下几个方面：

禁止在从表中插入包含主表中不存在的关键字的数据行；

禁止会导致从表中的相应值孤立的主表中的外关键字值改变；

禁止删除在从表中的有对应记录的主表记录。

SQL Server 提供了一些工具来帮助用户实现数据完整性，其中最主要的是：约束

（Constraint ）、缺省值（Default ）、规则（Rule ）、和触发器（Trigger ）。触发器将在后面的章节中介绍。

5.2.3 约束（constraint ）

1 DEFAULT约束

使用DEFAULT 约束，如果用户在插入新行是没有显示为列提供数据，系统会将默认支赋给该列。默认值约束的定义格式为： [CONSREAINT constraint_name]

DEFAULT constant_expression

其中，constraint_name参数指出所建立的默认值约束名称。Constant_expression表达式为列提供默认值。在使用默认约束是，还应该注意以下两点：1. 每列只能有一个默认约束。2. 约束表达式不能参照表中的其他列和其他表、视图或存储过程。

例1：新建teacher 数据表，并在Tsex 字段上添加缺省约束，其缺省值为‘男’。

create table teacher(Tno char(4), Tname char(6), Tsex char(2) constraint df_sex default ‘男’,Tdept char(20) )

例2：修改teacher 数据表，并在Tdept 字段上添加缺省约束，其缺省值为‘测绘系’。 Alter table teacher add constraint DF_dept ‘测绘系’ for Tdept

例3：修改teacher 数据表，将缺省约束DF_sex删除掉。

Alter table teacher drop constraint DF_sex

2 PRIMARY KEY约束

在数据库的每个表中，经常有通过一列或者多个列，唯一的标识表中的每一行。就好像我们平时使用的身份证，能够唯一的标识每个人一样。这样的一列或者多个列，被称为主键，通过主键，可以强制表的实体完整性。每一个表中只有一个PRIMARY KEY约束，更简单的说，他是通过建立唯一索引保证指定列的实体完整性。在使用PRIMARY KEY约束时，该列的空值属性必须定义为NOT NULL，也就是说拥有主键的那一列，不能为空。由于PRIMARY KEY约束确保唯一数据，所以经常用来定义标识列。标识列就是表中已经指派了标识属性的列。标识属性生成唯一数字。

建立主键不仅可以保证表内数据的完整性，而且在为表建立主键的同时，Microsoft SQL Server 能够通过为主键创建唯一索引强制数据的唯一性。如果在PRIMARY KEY约束中未指定索引类型时，默认情况下所建立的索引为簇索引。该索引只能通过删除PRIMARY KEY 约束或其相关表的方法来删除，而不能使用DROP INDEX语句删除。当PRIMARY KEY 约束由另一张表的FOREIGN KEY约束引用时，不能删除PRIMARY KEY约束；要删除它，必须先删除FOREIGN KEY约束。

通常建立一列约束时，我们称之为列级PRIMARY KEY约束，应用于多列时，称之为表级PRIMARY KEY约束。列级PRIMARY KEY约束的定义格式为：

[CONSTRAINT constraint_name]

PRIMARY KEY [CLUSTERED | NONCLUSTERED]

表级PRIMARY KEY约束定义风格为：

[CONSTRAINT constraint_name]

PRIMARY KEY [CLUSTERED | NONCLUSTERED]

{(column[,…n])}

例4：新建department （部门）数据表，并在Dno （部门编号）字段上建立聚簇型主键约束pk_no。

create table department (Dno char(2) constraint pk_no primary key clustered, Dname char(20) )

例5：新建xk （选课）数据表，并在Sno （学号）和Cno （课程号）两个字段上建立表级主键约束key_xk。

create table xk(sno char(10) not null, cno char(4) not null, constraint key_xk primary key(sno,cno))

同样可以使用Alter table语句添加和删除主键约束。

3 UNIQUE约束

该约束应用于表中的非主键列，UNIQUE 约束保证一列或者多列的试题完整性，确保这些猎不会输入重复的值。例如，表中UserName 列为主键，但是其中还包括身份证号码列，由于所有身份证号码不可能出现重复，所以可以在此列上建立UNIQUE 约束，确保不会输入重复的身份证号码。

它与PRIMARY KEY约束的不同之处在于，UNIQUE 约束可以建立在多个列之上，而PRIMARY KEY约束在一个表中只能有一个。同样，对于一列的UNIQUE 约束，我们称之为列级UNIQUE 约束，对于多列的UNIQUE 约束，我们称之为表级UNIQUE 约束。下面给出列级UNIQUE 约束的定义格式：

[CONSTRAINT constraint_name]

UNIQUE [CLUSTERED | NONCLUSTERED]

使用UNIQUE 约束的过程中，还需要注意，如果要对允许空值的列强制唯一性。可以允许空值的列附加UNIQUE 约束，而只能将主键的约束附加到不允许空值的列。但UNIQUE 约束不允许表中受约束列有一行以上的值同时为空。

例6：新建test1（学生）数据表，在Sno （学号）设置聚簇型主键约束pk_sno，在Sname （姓名）字段上设置非空非聚簇型唯一值约束un_sname。

(sno char(10) constraint pk_sno primary key clustered,

sname char(6) constraint un_sname unique nonclustered not null,

smajor char(16))

4 CHECK约束

CHECK 约束的主要作用是限制输入到一列或多列中的可能值，从而保证SQL Server数据库中数据的域完整性。例如，可以在建立用户使用库时，强制用户的密码在10位以上。每个标允许建立多个CHECK 约束。同样，我们可以为表中的每个列建立约束，每个列可以拥有多个CHECK 约束，但是如果使用CREATE TABLE语句，只能为每个列建立一个CHECK 约束。如果CHECK 约束被应用于多列时，他必须被定义为表级CHECK 约束。 下面给出列级Check 约束的定义格式： [CONSTRAINT constraint_name]

CHECK (logical_expressin)

例7：新建test2（选课）数据表，并将grade 字段设置为缺省值为0并可取空值，并在该字段建立检查约束ck_grade使其取值在0到100之间。

CREATE TABLE test2

(sno char(10),cno char(4),

grade numeric(5,1) DEFAULT 0 NULL CONSTRAINT ck_grade

CHECK (grade>=0 AND grade

5 FOREIGN KEY约束

FOREIGN KEY约束为表中的一列或者多列数据提供数据完整性参照。通常是与

PRIMARY KEY约束或者UNIQUE 约束同时使用的。

下面给出列级FOREIGN KEY约束的定义格式：

CREATE TABLE table_name

( field_name datatype

[ CONSTRAINT constraint_name ]

[ [ FOREIGN KEY REFERENCES ref_table [ ( ref_column) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ] [,…n]

例8：新建test3（选课）数据表，在Sno 字段上建立引用Student 表Sno 字段的外键约束fk_s_sno，使当student 表sno 字段修改时同步更新test3表中对应sno 字段的值；在Cno 字段上建立引用Course 表Cno 字段的外键约束fk_c_cno。

(sno char(10) constraint fk_s_sno foreign key references student(sno)

on update cascade,

cno char(4) constraint fk_c_cno foreign key references course(sno),

grade numeric(5,1) )

6 Identity Column

可以通过使用标识列（设定处置和增加值, 若缺省都默认为1）为整型字段设定字段值自动添加，格式为IDENTITY [ ( seed , increment )]。 标识

例9：新建test4（学生）数据表，在Sno 字段上设置标识功能实现学号自动从1开始自动增1。Create table test4 (sno int identity, sname char(6))

例10：新建test5（学生）数据表，在Sno 字段上设置标识功能实现学号自动从10001开始自动增1。Create table test5 (sno int identity(10001,1), sname char(6))

5.2.4 默认值（Defaults ）

当插入一个新行时，若某个列没有明确指定数据值，该列将自动使用指定的缺省值。缺省值－可以是一个常量、一个内置函数、一个表达式或者一个全局变量。一般在创建表时，应使用缺省。

使用T －SQL 创建‘缺省值’对象和将‘缺省值’对象绑定到字段。

创建‘缺省值‘对象语句为：CREATE DEFAULT default_name AS

constraint_expression

将缺省值对象绑定到相应字段语句格式为：

SP_BINDEFAULT default_name , object_name

解除字段上的缺省值对象语句格式为：SP_UNBINDEFAULT object_name

例11：创建一个缺省值对象df_dept，默认值设为‘土木学院’。

CREATE DEFAULT df_dept AS ‘土木学院’

例12：将缺省值对象df_dept绑定到student 表sdept 字段上。

SP_BINDEFAULT ‘df_sdept’, ‘student.sdept ’

例13：解除student 表sdept 字段缺省值对象。

SP_UNBINDEFAULT ‘student.sdept ’

5.2.5 规则（Rule ）

规则类似于检查约束，因为它们都是限制输入到某个列的值。然而，与检查约束不同的是，检查约束只检查相对简单的值，而规则可以基于条件表达式或者值的列表限制数据值。与检查约束不同的另一点是，每个列只能有一个规则，并且SQL Server规则是作为单独的数据库对象存储的。每个列只能有一个规则，而一个规则可以绑定到多个列上。规则还可以应用到用户定义的数据类型上。

提示：在每个列上只能有一个规则，而在同一个列上可以有多个检查约束。常用的办法是，当可以选择时，最好使用约束而不是规则。

使用T －SQL 创建‘规则’对象和将‘规则’对象绑定到字段。

创建‘规则’对象语句为：CREATE RULE rule_name AS condition_expression 将‘规则’对象绑定到相应字段语句格式为：

SP_BINDRULE rule_name, object_name

解除字段上的‘规则’对象语句格式为：SP_UNBINDRULE object_name

例11：创建一个规则对象rule_age，使其取值范围为15到30岁之间。

CREATE RULE rule_age AS @value >=15 and @value=

例12：将规则对象rule_age绑定到student 表sage 字段上。

sp_bindrule ‘rule_age’, ‘student.sage ’

例13：解除student 表sage 字段规则对象。

sp_unbindrule ‘student.sage ’

5.2.6 触发器（Trigger ）

1 触发器的概念

触发器是一种特殊类型的存储过程，与表紧密相连。它是通过事件进触发而被执行的一段 T-SQL 语句，能进行复杂的逻辑处理。当用户操作表中数据时，触发器将自动执行。 触发器的作用：

① 级联修改数据库中的相关表

② 执行比核查约束更为复杂的约束操作

③ 拒绝或回滚违反参考完整性的操作

④ 比较表修改前后数据之间的差别，并根据差别采取相应的操作

触发器的分类：

（1）AFTER 类型触发器

这类触发器将在表中的数据变动（ INSERT 、UPDATE 、DELETE ）完成以后才被激发，是为了对变动的数据进行检查。如果发现错误，将拒绝或回滚变动的数据（rollback ），一个表可以创建多个AFTER 类型的触发器。

（2）INSTEAD OF 类型触发器

INSTEAD OF触发器是SQL SERVER2000中新增的功能，这种类型的触发器将在数据变动之前被激活，并取代数据的操作（ INSERT 、UPDATE 、DELETE ），转而去执行触发器定义的操作。

注意：触发器是针对某一具体操作触发的，所以在定义触发器时必须指定触发操作： INSERT 、UPDATE 、DELETE 。至少指定一种。如果时AFTER 触发器可以同时指定多个，如果是INSTEAD OF触发器只能指定一个。

2 触发器的工作原理

当向数据表执行INSERT 、UPDATE 、DELETE 语句时，若该表设置了触发器，则SQL SERVER 将根据不同的操作自动生成一个或两个临时表：inserted 表、deleted 表，同时将操作数据送入inserted 表或deleted 表。

inserted表和deleted 表是SQL SERVER为触发器创建的临时表，存储在内存中，不是存储在数据库中，不允许用户直接对其修改。它的表结构和定义触发器相关表的结构相同。触发器工作完成后，与之相关的逻辑表将自动删除。

（1）INSERT 触发器的工作原理

当使用INSERT 语句向数据表插入一条记录，相关的INSERT 触发器将自动触发执行。此时，INSERT 触发器自动创建一个inserted 表，插入的记录被同时添加到inserted 表和数据表中，触发器检测inserted 表和数据表用于确定INSERT 触发器中的操作是否执行。

（2）DELETE 触发器的工作原理

当使用 DELETE 语句向数据表删除记录时，与之相关的DELETE 触发器将自动触发执行。

此时 DELETE 触发器自动创建一个deleted 表，删除的记录从数据表中被删除，并放入deleted 表中。因此，deleted 表和数据表没有相同的行，触发器检测deleted 表和数据表用于确定DELETE 触发器中的操作是否执行。

（3）UPDATE 触发器的工作原理

UPDATE语句相当于在数据表中先执行了DELETE 操作，后执行了INSERT 操作，即先删除旧记录，马上插入新记录。当数据表使用UPDATE 语句修改记录时，UPDATE 触发器将自动触发执行。此时，UPDATET 触发器自动创建一个inserted 表和deleted 表，将要删除的记录放入deleted 表，将新的记录放入inserted 表。因此，触发器检测inserted 表、deleted 表和数据表，用于确定是否修改了数据行和UPDATE 触发器中的操作是否执行。

最后，当确定触发器中的操作是不可执行的，用 rollback 语句撤销所有事务，使数据表回来语句执行前的状态。

3 使用T-SQL 语言管理触发器

创建触发器的语句格式：

CREATE TRIGGER trigger_name ON{table|view}

[WITH ENCRYPTION]

{ FOR | AFTER | INSTEAD OF }{ [UPDATE][,][ INSERT ] [ , ] [DELETE ]} AS

sql_statement[,…n]

参数说明：

① trigger_name：指定触发器的名称。虽然触发器时基于数据表创建的，但是它在数据库中是唯一的。 ② table|view：创建触发器的表或视图。只有INSTEAD OF触发器才能基于视图创建。

③ WITH ENCRYPTION：加密触发器定义语句。和用于视图的加密语句一样，也是不可逆的。

④ FOR | AFTER | INSTEAD OF：指定触发器的类型。如果指定FOR 关键字和AFTER 关键字，表示创建的是AFTER 触发器；如果指定INSTEAD OF，表示创建的是INSTEAD OF触发器。

⑤ [UPDATE][,][ INSERT ] [ , ] [DELETE ]：指定在表上执行哪些数据修改语句时将激活触发器。必须指定一个选项。允许以任意顺序组合的这些关键字。INSTEAD OF触发器中每一种操作只能存在一个。

⑥ sql_statement：定义触发器的语句（文本）。指定过程要执行的操作。

例14：在student 表上创建一个after 类型的名称为student-update 的触发器，实现当更新student 表里的学号时，同时去级联更新这个同学在选课表里对应的学号，并给出修改结果信息。

CREATE TRIGGER student_update ON student FOR UPDATE

AS

IF update(sno)

begin

update sc set sno=(select sno from inserted)

where sno=(select sno from deleted )

print ‘级联修改成功！’

end

ELSE

print ‘没有对应的选课记录！’

5.3 实验内容

要求使用企业管理器和SQL 语句来完成如下内容。

注意：如果创建过程中提示数据库中已存在同名对象，请自行更换相应对象名称。

（1）在查询分析器中使用CREATE TABLE 语句，在JXGL 数据库中创建符合下表中完整性约束条件的学生表S 。

列名 数据类型 能否空值 默认值 键/索引 说明

SNO CHAR(6) 否 主键、聚集索引 学号

NAME CHAR(8) 否 唯一约束 姓名

AGE NUMERIC(2) 年龄

SEX CHAR(2) ' 男' 性别

DEPT CHAR(10) 所在系

（2）在查询分析器中使用CREATE TABLE 语句，在JXGL 数据库中创建符合下表中完整性约束条件的选课表temp 。

能否空列名 数据类型 检查 键/索引 说明 值

组合主键、聚集索引 SNO CHAR(6) 否 学号 外键 student(sno)

组合主键、聚集索引

CNO CHAR(4) 否 外键 course (cno) 课程号

主表更新采用级联

SCORE NUMERIC(2) 0~100 成绩

注：组合主键、聚集索引定义在SNO 和CNO 上；外键上还需定义一个非聚集索引。

（3）使用企业管理器为JXGL 数据库创建一个age_rule规则，并将其绑定到学生表student 的Sage 列，使Sage 在10到50之间取值。

（4）使用查询分析器为JXGL 数据库创建一个sex_rule规则，并将其绑定到学生表Student 的Ssex 列，使Ssex 只能取值为’男’或’女’。

（5）使用企业管理器为JXGL 数据库创建一个dept_default默认，并将其绑定到学生表Student 的Sdept 列，设置该列的默认值为’土木学院’。

（6）使用企业管理器为JXGL 数据库创建一个数据表，表名、列名和数据类型自定，给其中一个字段设置为标识列，并指明初始值和增加量（自定）。

（7）使用企业管理器为JXGL 数据库创建一个数据表Test ，列名和数据类型自定，利用设计表窗口和属性对话框创建和修改约束，掌握企业管理器建立主键约束、外键约束和检查约束等方面的技能。

（8）使用T-SQL 语句管理触发器

① 在 course 表中创建一个触发器，禁止更新或者添加数据的操作。

② 在表 TEACHERS 中创建 AFTER 类型触发器 trigger_ change ，限制每次工资 (pay) 的变动不能超过 2000 。注意：通过比较 inserted 表和 deleted 表的 pay 来判断。

在查询分析中输入下列语句。

update teachers

set pay=6000

where tno='001'

问题： 该语句执行结果是什么？查看数据表该条记录是否被修改 ？ 如果没有被修改，分析其原因。

③ 在 TEACHERS 表中创建一个 AFTER 类型触发器 trigger_ del ，监控删除的教师记录。（如果被删除的教师是 “ 讲师 ” 职称，则提示不能删除这个教师，否则可以删除）注意：根据 deleted 表中的记录来判断。在创建完触发器后，在查询分析中输入下列语句，验证触发器是否能正常工作。

delete teachers where tno='030'

④ 在 teachers 表上建立一个 AFTER 类型的触发器，监控对老师工资的更新，当更新后的工资比更新前小时，取消操作，并给出提示信息，否则允许。

有哪两种方法？

约束类型？它们分别是什么？ 触发器的工作原理？

思考题 （1）SQL Server实现数据完整性（2）SQL Server有几种（3）简述SQL Server