06实现数据完整性约束.pptx

1、数据库技术与应用,袁宝库 ,实现数据完整性约束,数据完整性基本概念 实现声明完整性 实现过程完整性,数据完整性基本概念,数据的完整性是为了防止数据库中存在不符合语义的数据。 这些加在数据库数据之上的语义约束条件就是数据完整性约束条件。 这些约束条件作为表定义的一部分存储在数据库中。 DBMS检查数据是否满足完整性条件的机制就称为完整性检查。,完整性约束条件的作用对象,列级约束 对数据类型的约束 对数据格式的约束 对取值范围或取值集合的约束 对空值的约束 元组约束 元组中各个字段之间的联系的约束，如：开始日期小于结束日期。 关系约束 是若干元组之间、关系之间的联系的约束。,实现数据完整性的方法,

2、一种是在定义表时声明数据完整性，称为声明完整性， 另一种是在服务器端编写触发器来实现，称为过程完整性。 在执行对数据的增、删、改操作时，数据库管理系统自动检查用户定义的完整性约束条件。,实现声明完整性,主码约束 每个表只能有一个PRIMARY KEY约束； 用PRIMARY KEY约束的列取值不能有重复，而且不允许有空值； 添加主码约束的语法格式： ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY （ , n ）,实现声明完整性,例：对雇员表和工作表添加主码约束 ALTER TABLE 雇员表 ADD CONSTRAINT PK_EMP PRIMARY

3、 KEY （雇员编号） ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY （工作编号）,UNIQUE约束,用于限制在一个列中不能有重复的值。 用在事实上具有惟一性的属性列上，比如每个人的身份证号码、驾驶证号码等均不能有重复值。 注意： 允许有一个空值； 在一个表中可以定义多个UNIQUE约束； 可以在一个列或多个列上定义UNIQUE约束。,添加UNIQUE约束,添加UNIQUE约束的语法格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE（ , n ） 例为雇员表的“电话”列添加UNIQUE约束。 ALTE

4、R TABLE 雇员表 ADD CONSTRAINT UK_SID UNIQUE（电话）,外码约束,实现引用完整性。 外码所引用的列必须是有PRIMARY KEY约束或UNIQUE约束的列。 添加FOREIGN KEY约束的语法格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY（） REFERENCES 引用表名（）,示例,例为雇员表的工作编号添加外码引用约束。 ALTER TABLE 雇员 ADD CONSTRAINT FK_job_id FOREIGN KEY （工作编号）REFERENCES 工作表 （工作编号）,DEFAULT约束,用于

5、提供列的默认值。 只有在向表中插入数据时才检查DEFAULT约束。 添加DEFAULT约束的语法格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 默认值 FOR 列名 例定义雇员表的工资的默认值为1000。 ALTER TABLE 雇员 ADD CONSTRAINT DF_SALARY DEFAULT 1000 FOR 工资,CHECK约束,用于限制列的取值在指定的范围内，使数据库中存放的值都是有意义的。 系统在执行INSERT语句和UPDATE语句时自动检查CHECK约束。 CHECK约束可约束同一个表中多个列之间的取值关系。 添加CHECK约束的

6、语法格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK （逻辑表达式）,示例,限制雇员的工资必须大于等于200。 ALTER TABLE 雇员 ADD CONSTRAINT CHK_Salary CHECK ( 工资 = 200 ) 限制工资表的最低工资小于等于最高工资。 ALTER TABLE 工作 ADD CONSTRAINT CHK_Job_Salary CHECK( 最低工资 = 最高工资 ),实现过程完整性,过程完整性是指在服务器端通过编写实现约束的一段代码来实现数据完整性约束，这段代码就称为触发器。 触发器是用编程的方法实现复杂的商业规则，它可

7、以实现一般的数据完整性约束实现不了的复杂的完整性约束。,事务的基本概念,事务 事务的特征 SQL事务处理模型,事务,事务是用户定义的数据操作系列，这些操作作为一个完整的工作单元，一个事务内的所有语句被作为一个整体，要么全部执行，要么全部不执行。 例如：对于一个转帐活动：A帐户转帐给B帐户n元钱，这个活动包含两个动作： 第一个动作：A帐户 n 第二个动作：B帐户 n,事务的特征,原子性（Atomicity） ：指事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。 一致性（Consistency） ：指事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。 隔离性（Isol

8、ation） ：指数据库中一个事务的执行不能被其它事务干扰。 持久性（Durability） ：也称为永久性指事务一旦提交，则其对数据库中数据的改变就是永久的。,事务的特征,保证事务的ACID特性是事务处理的重要任务。事务的ACID特性可能遭到破坏的因素有： 多个事务并行运行时，不同事务的操作有交叉情况； 事务在运行过程中被强迫停止。,SQL事务处理模型,隐式事务：隐式事务是每一条数据操作语句都自动地成为一个事务。 显式事务：有显式的开始和结束标记的事务。 ISO事务处理模型 T-SQL事务处理模型,ISO事务处理模型,明尾暗头事务的开头是隐含的，事务的结束有明确标记 A事务结束符 COMMI

9、T：事务成功结束符， ROLLBACK：事务失败结束符， B事务提交方式 自动提交：每条SQL语句为一个事务 指定位置提交：在事务结束符或程序正常结束处提交,ISO事务处理模型,明尾暗头事务的开头是隐含的，事务的结束有明确标记 C事务起始/终止位置 程序的首条SQL语句或事务结束符后的语句。 在程序正常结束处或COMMIT语句处成功终止； 在程序出错处或或ROLLBACK处失败终止。,示例,UPDATE 支付表 SET 帐户总额 帐户总额 n WHERE 帐户名 A UPDATE 支付表 SET 帐户总额 帐户总额 n WHERE 帐户名 B COMMIT,T-SQL事务处理模型,每个事务都有

10、显式的开始和结束标记。 事务的开始标记是： BEGIN TRANSACTION | TRAN 事务的结束标记为： COMMIT TRANSACTIONTRAN ROLLBACK TRANSACTIONTRAN,示例,例如前边的转帐例子用Transact-SQL事务处理模型描述为： BEGIN TRANSACTION UPDATE 支付表 SET 帐户总额 帐户总额 n WHERE 帐户名 A UPDATE 支付表 SET 帐户总额 帐户总额 n WHERE 帐户名 B COMMIT,触发器,是一种特殊的存储过程， 不需要由用户调用执行，而是当用户对表中的数据进行UPDATE、INSERT或DE

11、LETE操作时自动触发执行的。 触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。,触发器的优点,完成比CHECK约束更复杂的数据约束。 为保证数据库性能而维护的非规范化数据。 可实现复杂的商业规则。 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。,创建触发器,语法格式： CREATE TRIGGER 触发器名称 ON 表名 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS SQL 语句 . n ,两个逻辑工作表,DELETED表存储DELET

12、E和UPDATE语句所影响的行的副本。 INSERTED表存储INSERT和UPDATE语句所影响的行的副本。,示例,创建限制最低工资必须大于等于400的触发器。 CREATE TRIGGER tri_job_salary1 ON 工作表 FOR INSERT, UPDATE AS IF EXISTS( SELECT * FROM INSERTED WHERE 最低工资 400 ) BEGIN PRINT 最低工资必须大于等于400 ROLLBACK END,示例,创建实现限制最低工资必须小于最高工资的触发器。 CREATE TRIGGER tri_job_salary2 ON 工作表 FOR

13、 INSERT, UPDATE AS IF EXISTS(SELECT * FROM INSERTED WHERE 最低工资 = 最高工资 ) BEGIN PRINT 最低工资必须小于最高工资 ROLLBACK END,示例,创建实现限制雇员的工资必须在工作表的相应工作的最低工资和最高工资之间。 CREATE TRIGGER tri_emp_salary ON 雇员表 FOR INSERT, UPDATE AS IF EXISTS (SELECT * FROM INSERTED a JOIN 工作表 b ON a.工作编号 = b.工作编号 WHERE 工资 NOT BETWEEN 最低工资

14、AND 最高工资 ) ROLLBACK,限制更新数据的触发器,限制将SC表中不及格学生的成绩改为及格。 CREATE TRIGGER tri_grade ON SC FOR UPDATE AS IF UPDATE(Grade) IF EXISTS(SELECT * FROM INSERTED JOIN DELETED ON INSERTED.Sno = DELETED.Sno WHERE INSERTED.Grade = 60 AND DELETED.Grade 60) ROLLBACK,限制删除的触发器,限制删除SC表中成绩不及格学生的修课记录。 CREATE TRIGGER tri_del_grade ON SC FOR DELETE AS IF EXISTS(SELECT *