《第5章 实现数据完整性约束(07-04)》由会员分享，可在线阅读，更多相关《第5章 实现数据完整性约束(07-04)（35页珍藏版）》请在金锄头文库上搜索。
1、第5章 数据完整性,5.1 数据完整性基本概念 5.2 实现声明完整性 5.3 实现过程完整性,5.1 数据完整性基本概念,数据的完整性是为了防止数据库中存在不符合语义的数据。这些加在数据库数据之上的语义约束条件就是数据完整性约束条件。这些约束条件作为表定义的一部分存储在数据库中。DBMS检查数据是否满足完整性条件的机制就称为完整性检查。,5.1.1 完整性约束条件的作用对象,列级约束对数据类型的约束对数据格式的约束对取值范围或取值集合的约束对空值的约束元组约束元组中各个字段之间的联系的约束，如：开始日期小于结束日期。关系约束是若干元组之间、关系之间的联系的约束。,5.1.2 实现数据完整性的
2、方法,一种是在定义表时声明数据完整性，称为声明完整性。另一种是在服务器端编写触发器来实现，称为过程完整性。DBMS 系统必须提供定义完整性条件的机制、提供完整性检查的方法和违约的处理在执行对数据的增、删、改操作时，数据库管理系统自动检查用户定义的完整性约束条件。,假设有两个工作表：雇员表 雇员（雇员编号，雇员名，工作编号，工资，电话）工作表 工作（工作编号，最低工资，最高工资）其中：雇员编号、工作编号分别是表的主码。 工作编号是雇员表的外码，引用工作表中的工作编号,5.2 实现声明完整性,1主码约束每个表只能有一个PRIMARY KEY约束；用PRIMARY KEY约束的列取值不能有重复，而且
3、不允许有空值；可在数据表定义时体现，也可以根据需要添加。添加主码约束的语法格式：ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY （ , n ）,例：对雇员表和工作表添加主码约束,ALTER TABLE 雇员表 ADD CONSTRAINT PK_EMP PRIMARY KEY （雇员编号）ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY （工作编号）,1主码约束,2UNIQUE约束,用于限制在一个列中不能有重复的值。作用于具有惟一性的属性列上。注意：在一个表中可以定义多个UNIQUE约束；可以在一个列
4、或多个列上定义UNIQUE约束。,添加UNIQUE约束,添加UNIQUE约束的语法格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE（ , n ）例为雇员表的“电话”列添加UNIQUE约束。ALTER TABLE 雇员表 ADD CONSTRAINT UK_SID UNIQUE（电话）,3外码约束,实现引用完整性。外码所引用的列须是有PRIMARY KEY约束的列。添加FOREIGN KEY约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY（） REFERENCES 引用表名（）,示例,例为雇员表
5、的工作编号添加外码引用约束。ALTER TABLE 雇员 ADD CONSTRAINT FK_job_id FOREIGN KEY （工作编号）REFERENCES 工作表 （工作编号）,4DEFAULT约束,用于提供列的默认值。只有在向表中插入数据时才检查DEFAULT约束。添加DEFAULT约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 默认值 FOR 列名例定义雇员表的工资的默认值为1000。ALTER TABLE 雇员 ADD CONSTRAINT DF_SALARY DEFAULT 1000 FOR 工资,5、CHECK约束,用于
6、限制列的取值在指定的范围内，使数据库中存放的值都是有意义的。系统在执行INSERT语句和UPDATE语句时自动检查CHECK约束。CHECK约束可约束同一个表中多个列之间的取值关系。添加CHECK约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK （逻辑表达式）,示例,例1限制雇员的工资必须大于等于200。ALTER TABLE 雇员 ADD CONSTRAINT CHK_SalaryCHECK ( 工资 = 200 )例2限制工资表的最低工资小于等于最高工资。ALTER TABLE 工作 ADD CONSTRAINT CHK_Job_Salary
7、 CHECK( 最低工资 = 最高工资 ),5.3 实现过程完整性,过程完整性是指在服务器端通过编写实现约束的一段代码来实现数据完整性约束，这段代码就称为触发器。触发器是用编程的方法实现复杂的商业规则，它可以实现一般的数据完整性约束实现不了的复杂的完整性约束。声明完整性不提供数据库间的引用完整性。,5.3.1 事务基本概念,事务（Transaction）是作为完整的工作单元执行的一系列操作。如果一个事务中的所有操作都成功，则事务成功，其对数据库的更改都会成为永久性的更改。如果事务中的任何一个操作失败，则整个事务失败，其中所完成的操作均被取消，所有对数据的更改均无效。,事务的三种类型,自动提交事
8、务每一条对数据的增、删、改语句都自动地构成了一个事务。显式事务是用户定义的事务，有显式的开始（BEGIN TRANSACTION ）和结束标记（COMMIT（正常结束）和ROLLBACK（异常结束） ）。 隐式事务事务的开始是隐式的，以前一个事务结束后的第一个SQL语句作为下一个事务的开始，但每个事务必须有显式的结束标记。SQL Server也支持上述三种事务。,自动事务是SQL Server 默认的事务模式。SQL 语句成功执行后，被自动提交，错误时则被自动回滚。显式事务：是用户定义的事务，有显式的开始（BEGIN TRANSACTION ）和结束标记（COMMIT（正常结束）和ROLLBA
9、CK（异常结束）隐式事务：SQL 中用set implicity_transaction on进入隐式事务，用set implicity_transaction off退出隐式事务不需要用BEGIN TRANSACTION 语句标识事务的开始，但需要有事务结束的语句COMMIT RANSACTION,5.3.2 触发器概述,是一种特殊的存储过程，它被捆绑在数据表或者视图中。不需要由用户调用执行，而是当用户对表中的数据进行 UPDATE、INSERT或DELETE操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数
10、据完整性约束的功能。,触发器的优点,完成比参照完整性约束和CHECK约束更复杂的数据约束。参照完整性约束不能跨数据库的实现参照完整性约束；CHECK约束只能引用当前列（列级CHECK约束）或当前表（表级CHECK约束）中的列值，而触发器则可引用其他表中的列值。,使用定义的错误信息实现数据库中多张表的级联修改。如在数据完整性遭到破坏时，定义好的错误信息或动态自定义的错误信息。比较数据库更新前后数据的状态。触发器可以评估由更新操作引起的数据修改前后的状态。,5.3.3 SQL Server触发器,SQL Server提供以下两种触发方式：后触发：在触发操作（INSERT、 UPDATE或DELET
11、E）执行完成，并处理过所有约束后激活触发器，这种方式称做后触发。 如果触发操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。但在视图上不能采用后触发方式定义触发器。替代触发：指定执行触发器而不是执行触发 SQL语句（INSERT、 UPDATE或DELETE ），从而替代触发语句的操作。 在表或视图上，每个 Insert、Update 或 Delete语句最多可以定义1个 INSTEAD OF 触发器，但可以在每个具有INSTEAD OF 触发器的视图上定义视图。,DELETED 和 INSERTED表,触发器语句中使用两个特殊的临时工作表：DELETED表和INSERTED表。这两个
12、表示用户执行数据的增、删、改操作时，由SQL Server自动创建和管理的。 这两个表驻留在内存中，其结构与触发器所作用的基本表的结构相同，并且只可以被触发器使用。当触发器结束时，系统自动释放这两个表的空间。在触发器中，可以像对待普通表一样对这两个表进行查询，并用于测试某些数据修改的效果及设置触发器执行的条件。但在触发器中不能直接对这两个表中的数据进行更改。,两个逻辑工作表,DELETED表存储DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到DELETED表中。DELETED表和触发器表通常没有相同的行。INSERTED表存储
13、INSERT和UPDATE语句所影响的行的副本。在一个插入或更新操作中，新建行也同时添加到INSERTED表和触发器表中。INSERTED表中的行是触发器表中新行的副本。,5.3.4 SQL Server 触发器操作,建立触发器CREATE TRIGGER 触发器名称ON 表名 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS SQL 语句 . n ,AFTER：后触发 仅指定 FOR 关键字，则 AFTER是默认设置 。 不能在视图上定义 AFTER 触发器 INSTEAD OF：替代式触发 在表或视图上，每个 Insert、Updat
14、e 或 Delete语句最多可以定义一个 INSTEAD OF 触发器。可以在每个具有INSTEAD OF 触发器的视图上定义视图。,建立触发器示例,创建限制最低工资必须大于等于400的触发器。CREATE TRIGGER tri_job_salary1 ON 工作表 FOR INSERT, UPDATE AS IF EXISTS( SELECT * FROM INSERTED WHERE 最低工资 = 最高工资 ) BEGIN PRINT 最低工资必须小于最高工资 ROLLBACK END,建立触发器示例,创建实现限制雇员的工资必须在工作表的相应工作的最低工资和最高工资之间。CREATE TRIGGER tri_emp_salaryON 雇员表FOR INSERT, UPDATEAS IF EXISTS (SELECT * FROM INSERTED a JOIN 工作表 b ON a.工作编号 = b.工作编号 WHERE 工资 NOT BETWEEN 最低工资 AND 最高工资 )ROLLBACK,限制更新数据的触发器示例,
