数据库系统第05章 数据库完整性

1、An Introduction to Database System1An Introduction to Database System2第五章第五章 数据库完整性数据库完整性n数据的数据的正确性正确性和和相容性相容性n防止不合语义的、不正确的数据进入数据库。防止不合语义的、不正确的数据进入数据库。例例: :学生的年龄必须是整数，取值在学生的年龄必须是整数，取值在6-506-50间；间； 学生的性别只能是男或女；学生的性别只能是男或女； 学生的学号一定是唯一的；学生的学号一定是唯一的； 学生所在的系必须是学校已有的系；学生所在的系必须是学校已有的系；n完整性：保证数据库能否真实地反映现实世界

2、完整性：保证数据库能否真实地反映现实世界.An Introduction to Database System3 完整性约束条件是数据模型的组成部分，完整性约束条件是数据模型的组成部分，约束数据库中数据的语义。约束数据库中数据的语义。 检查用户发出的操作请求是否违背了完整检查用户发出的操作请求是否违背了完整性约束条件性约束条件 1 1. .完整性约束的定义机制完整性约束的定义机制2.2.完整性的检查完整性的检查机制机制3.3.违约反应违约反应 第第五五章章 数据库完整性数据库完整性An Introduction to Database System4第第五五章章 数据库完整性数据库完整性5.1

3、 5.1 实体完整性实体完整性5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义完整性用户定义完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.5 5.5 触发器触发器An Introduction to Database System55.1 5.1 实体完整性实体完整性 在在CREATE TABLECREATE TABLE语句中用语句中用PRIMARY KEYPRIMARY KEY定义定义An Introduction to Database System65.1 5.1 实体完整性实体完整性 例例5.15.1 在数据库中，定义在数据库中，定义StudentStude

4、nt表的表的SnoSno属性为主码。属性为主码。定义在列级约束定义在列级约束： CREATE TABLE Student CREATE TABLE Student (Sno CHAR(8) , (Sno CHAR(8) , Sname CHAR(8), Sname CHAR(8), Sage INT); Sage INT); 定义在表级约束：定义在表级约束： CREATE TABLE Student CREATE TABLE Student (Sno CHAR(8) (Sno CHAR(8)， Sname CHAR(8),Sname CHAR(8), Sage INT Sage INT， );

5、);PRIMARY KEY,PRIMARY KEY(Sno););An Introduction to Database System75.1 5.1 实体完整性实体完整性 例例5.25.2 在在SCSC表中定义表中定义(Sno,Cno)(Sno,Cno)为主码。为主码。 CREATE TABLE SC CREATE TABLE SC ( Sno CHAR(5), ( Sno CHAR(5), Cno CHAR(2), Cno CHAR(2), Grade SMALLINT, Grade SMALLINT, PRIMARY KEY (Sno, Cno)PRIMARY KEY (Sno, Cno

6、) ); );An Introduction to Database System85.1 5.1 实体完整性实体完整性 在在CREATE TABLECREATE TABLE语句中用语句中用PRIMARY KEYPRIMARY KEY定义定义 码由单字段构成码由单字段构成： 定义可以为列级或表级约束；定义可以为列级或表级约束； 码由多字段构成：码由多字段构成： 定义只能为表级约束；定义只能为表级约束；An Introduction to Database System95.1 5.1 实体完整性实体完整性 (1)(1)完整性检查完整性检查 当当用户用户插入插入记录记录或或修改主码修改主码时，时

7、，DBMSDBMS自动自动检查：检查：每个主属性值是否为每个主属性值是否为空；空；主码值是否惟一。主码值是否惟一。 (2)(2)违约处理违约处理 拒绝操作。拒绝操作。An Introduction to Database System105.1 5.1 实体完整性实体完整性 (3)(3)检查主码是否惟一的方法检查主码是否惟一的方法 全表扫描全表扫描判断判断新新的的主码主码值在表中是否已存在。值在表中是否已存在。缺点缺点：效率低：效率低 利用利用主码的主码的B B索引索引优点优点：效率高：效率高An Introduction to Database System115.1 5.1 实体完整性实体

8、完整性keyF1F2F332520159379658471517304154656968255112 3066 783 715 20 25 30 4151 54 6568 69 71 7679 84 93An Introduction to Database System12第第五五章章 数据库完整性数据库完整性5.1 5.1 实体完整性实体完整性5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义完整性用户定义完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.5 5.5 触发器触发器An Introduction to Database System135.2 5.2 参

9、照完整性参照完整性 在在CREATE TABLECREATE TABLE语句中：语句中： 用用 FOREIGN KEY FOREIGN KEY 短语定义外码列；短语定义外码列； 用用 REFERENCES REFERENCES 短语指明外码参照哪个表的短语指明外码参照哪个表的主码。主码。An Introduction to Database System145.2 5.2 参照完整性参照完整性 例例5.35.3 在在SCSC表中的参照完整性。表中的参照完整性。 An Introduction to Database System155.2 5.2 参照完整性参照完整性 例例 在在SCSC表中的

10、参照完整性。表中的参照完整性。 An Introduction to Database System16 对被对被参照表和参照表进行参照表和参照表进行增、删、改增、删、改操作操作时进行检查时进行检查有四种情况可能破坏参照完整性：有四种情况可能破坏参照完整性：参照表中参照表中l插入新插入新元组；元组；l修改外码值；修改外码值；被参照表中被参照表中l删除元组删除元组；l修改主码值。修改主码值。5.2 5.2 参照完整性参照完整性An Introduction to Database System17 当参照完整性不一致时，系统采取以下策略：当参照完整性不一致时，系统采取以下策略：n拒绝执行拒绝执行

11、（默认策略）（默认策略） n级联操作级联操作（CASCADE） 当删除或修改被参照表的一个元组造成与当删除或修改被参照表的一个元组造成与参照表不一致时，则删除或修改参照表中所有参照表不一致时，则删除或修改参照表中所有不一致的元组。不一致的元组。n设置为空设置为空（SET-NULL） 当删除或修改被参照表的一个元组造成与当删除或修改被参照表的一个元组造成与参照表不一致时，则将参照表中所有造成不一参照表不一致时，则将参照表中所有造成不一致的元组对应属性置空。致的元组对应属性置空。5.2 5.2 参照完整性参照完整性 定义参照完整性时，除了定义外码，还应定义参照完整性时，除了定义外码，还应定义外码定

12、义外码是否级联是否级联、是否为空是否为空。An Introduction to Database System185.2 5.2 参照完整性参照完整性 例例5.45.4在在SCSC表中的参照完整性。表中的参照完整性。 An Introduction to Database System19例：要删除例：要删除StudentStudent关系中关系中Sno=Sno=20122151212012215121的的元组，元组，而而SCSC关系中关系中有有5 5个个元组元组的的SnoSno都都等于等于201215121201215121。n置空值删除置空值删除： 将将SCSC关系中所有关系中所有Sno=

13、Sno=201215121201215121的的元组元组SnoSno置空。置空。n级联删除级联删除： 将将SCSC关系中关系中所有所有5 5个个Sno=Sno=201215121201215121的的元组元组一起删除。依此类推可以继续级联下去。一起删除。依此类推可以继续级联下去。 n受限删除受限删除： 系统将拒绝执行此删除操作。系统将拒绝执行此删除操作。5.2 5.2 参照完整性参照完整性An Introduction to Database System20第第五五章章 数据库完整性数据库完整性5.1 5.1 实体完整性实体完整性5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义完

14、整性用户定义完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.5 5.5 触发器触发器An Introduction to Database System215.3 5.3 用户定义的完整性用户定义的完整性 使用使用CREATE TABLECREATE TABLE语句在定义属性的同时，语句在定义属性的同时，定义定义对属性值的限制对属性值的限制。 包括：包括： 列值非空列值非空(NOT NULL)(NOT NULL) 列值唯一列值唯一(UNIQUE)(UNIQUE) 检查列值是否满足一个布尔表达式检查列值是否满足一个布尔表达式(CHECK(CHECK子子句句) )。An Introd

15、uction to Database System225.3 5.3 用户定义的完整性用户定义的完整性An Introduction to Database System235.3 5.3 用户定义的完整性用户定义的完整性An Introduction to Database System245.3 5.3 用户定义的完整性用户定义的完整性An Introduction to Database System255.3 5.3 用户定义的完整性用户定义的完整性An Introduction to Database System265.3 5.3 用户定义的完整性用户定义的完整性An Introd

16、uction to Database System275.3 5.3 用户定义的完整性用户定义的完整性An Introduction to Database System285.3 5.3 用户定义的完整性用户定义的完整性 当往表中当往表中插入插入元组或元组或修改修改属性的值时，属性的值时，RDBMSRDBMS就就检查属性上的约束条件检查属性上的约束条件是否被满足，是否被满足，如果不满足操作被拒绝执行。如果不满足操作被拒绝执行。An Introduction to Database System295.3 5.3 用户定义的完整性用户定义的完整性 在在CREATE TABLECREATE TA

17、BLE语句可以用语句可以用CHECKCHECK短语定义元组短语定义元组上的约束条件，即元组级限制上的约束条件，即元组级限制 元组级限制可以设置不同属性之间的取值的相互元组级限制可以设置不同属性之间的取值的相互约束条件。约束条件。An Introduction to Database System305.3 5.3 用户定义的完整性用户定义的完整性 在在CREATE TABLECREATE TABLE语句可以用语句可以用CHECKCHECK短语定义元组短语定义元组上的约束条件，即元组级限制上的约束条件，即元组级限制 元组级限制可以设置不同属性之间的取值的相互元组级限制可以设置不同属性之间的取值的

18、相互约束条件。约束条件。 An Introduction to Database System31第第五五章章 数据库完整性数据库完整性5.1 5.1 实体完整性实体完整性5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义完整性用户定义完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.5 5.5 触发器触发器An Introduction to Database System325.4 5.4 完整性约束命名子句完整性约束命名子句 SQLSQL在提供了完整性约束命名子句在提供了完整性约束命名子句 CONSTRAINTCONSTRAINT，用来对完整性约束条件进行命名。用来

19、对完整性约束条件进行命名。 格式：格式： CONSTRAINT CONSTRAINT PRIMARY KEY PRIMARY KEY短语短语| | FOREIGN KEY FOREIGN KEY短语短语| | CHECK CHECK短语短语 放置在任何一个放置在任何一个约束短语的前面。约束短语的前面。An Introduction to Database System33An Introduction to Database System345.4 5.4 完整性约束命名子句完整性约束命名子句 格式格式：DROP DROP CONSTRAINT CONSTRAINT 先先删除删除，再再添加。添

20、加。An Introduction to Database System35第第五五章章 数据库完整性数据库完整性5.1 5.1 实体完整性实体完整性5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义完整性用户定义完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.5 5.5 触发器触发器An Introduction to Database System365.5 5.5 触发器触发器 它它是是定义定义在表上的一类由事件驱动的特殊过程在表上的一类由事件驱动的特殊过程 作用：作用： 用来实现当使用用来实现当使用update update 、insertinsert、dele

21、tedelete对对表进行操作时，表进行操作时，自动触发自动触发的处理方法；的处理方法； 实现复杂的业务要求，类似于实现复杂的业务要求，类似于约束约束 保证数据的一致性。保证数据的一致性。 An Introduction to Database System375.5 5.5 触发器触发器 After触发器：（默认类型）触发器：（默认类型） 触发触发Insert、Update、Delete动作之后执行。动作之后执行。 （表中数据已经变动之后）（表中数据已经变动之后） Instead of触发器：触发器： 当执行当执行Insert、Update、Delete操作时操作时用触用触发器中的操作来代替

22、该操作。发器中的操作来代替该操作。 (表中数据改动之前表中数据改动之前)An Introduction to Database System385.5 5.5 触发器触发器 格式格式：(P169) Create Trigger on for | after | instead of delete , insert , update as 触发器的类型触发器的类型触发事件触发事件An Introduction to Database System395.5 5.5 触发器触发器 Create Trigger T_InOn studentafter insertasprint 欢迎新同学的到来！欢

23、迎新同学的到来！ An Introduction to Database System405.5 5.5 触发器触发器 CREATE TRIGGER语句必须是批处理语句中语句必须是批处理语句中的第一条；的第一条； 只有只有表的拥有者表的拥有者可以创建触发器；可以创建触发器； 一个表只能创建一定数量的触发器；一个表只能创建一定数量的触发器； 在同一在同一模式模式下，触发器名必须惟一；下，触发器名必须惟一； 当触发器被激活时，只有触发条件为真时当触发器被激活时，只有触发条件为真时触发触发体才体才被被执行执行。An Introduction to Database System415.5 5.5 触

24、发器触发器(1)(1)触发器工作时建立两个表：触发器工作时建立两个表： inserted表：表： 存储存储insert和和update语句所影响的数据行副本语句所影响的数据行副本 insertedinserted中的行是触发器表中新行的副本。中的行是触发器表中新行的副本。 deleted表：表： 存储存储delete和和update语句所影响的数据行副本语句所影响的数据行副本 deleted中的行是从触发器表中删除的行。中的行是从触发器表中删除的行。 触发器作用的表触发器作用的表和和deleted表没有相同的行表没有相同的行 An Introduction to Database System

25、425.5 5.5 触发器触发器 (2)(2)这两个表的特点：这两个表的特点： 系统自动维护系统自动维护 对用户而言，是只读的对用户而言，是只读的 表结构与触发器作用的表结构相同表结构与触发器作用的表结构相同 与触发器同时存在与触发器同时存在 （触发器运行结束，系统自动删除这两表）（触发器运行结束，系统自动删除这两表）An Introduction to Database System435.5 5.5 触发器触发器 (3)(3)各种操作时触发器的工作：各种操作时触发器的工作： insertinsert触发器触发器被被触发触发激活激活 新记录会添加到基本表和新记录会添加到基本表和inserte

26、dinserted表。表。 deletedelete触发器触发器被触发激活被触发激活 被删除的记录会存放到被删除的记录会存放到deleteddeleted表中。表中。 相当于删除一条旧相当于删除一条旧记录记录, ,插入插入一条一条新纪录。新纪录。An Introduction to Database System445.5 5.5 触发器触发器 CREATE TRIGGER t_age on StudentFOR Insert,UpdateASIF( )=60) ) where sno=(select sno from inserted) 某工厂的仓库管理数据库的部分关系模式如下：某工厂的仓库

27、管理数据库的部分关系模式如下：n仓库仓库(仓库号仓库号,面积面积,负责人负责人,电话电话)n原材料原材料(编号编号,名称名称,数量数量,储备量储备量,仓库号仓库号) n订购计划订购计划(原材料编号原材料编号,订购数量订购数量)要求触发器实现：当仓库中的任一原材料的数量要求触发器实现：当仓库中的任一原材料的数量小于其储备量时，向订购计划表小于其储备量时，向订购计划表中插入该中插入该原材料原材料的订购计划，其订购量为储备量的的订购计划，其订购量为储备量的3倍倍练习练习n仓库仓库(仓库号仓库号,面积面积,负责人负责人,电话电话)n原材料原材料(编号编号,名称名称,数量数量,储备量储备量,仓库号仓库号

28、) n订购计划订购计划(原材料编号原材料编号,订购数量订购数量) CREATE TRIGGER t_订购订购 ON 原材料原材料 FOR 。 AS IF ) Insert into 订购计划订购计划 VALUES( ， ) Insert,Update(select 编号编号 from inserted)(select 数量数量 from inserted)(select 储备量储备量 from inserted)(select 储备量储备量* 3 from inserted)CREATE TRIGGER t_订购订购 ON sc FOR Insert,UpdateASIF (select 储备

29、量储备量 from inserted)= 18 and age = 60 局部变量局部变量Where中的中的任何表达式任何表达式An Introduction to Database System59补充补充规则规则2.规则的绑定与松绑规则的绑定与松绑u 若要若要规则发生作用，需将其与数据库表或用规则发生作用，需将其与数据库表或用户自定义对象联系起来。户自定义对象联系起来。 u绑定绑定：指定：指定规则作用于哪个表的哪一列或哪个规则作用于哪个表的哪一列或哪个用户自定义数据类型。用户自定义数据类型。 u松绑松绑：解除规则与对象的绑定。：解除规则与对象的绑定。 An Introduction to

30、Database System60补充补充2.规则的绑定与松绑规则的绑定与松绑(1)用存储过程用存储过程sp_bindrule绑定规则绑定规则 sp_bindrule rulename=规则名规则名, objname=对象名对象名,futureonly 例例2 绑定规则绑定规则age_rule到到Student表表Sage属性属性EXEC sp_bindrule age_rule, Student.Sage 只在自定义数只在自定义数据类型时用据类型时用An Introduction to Database System61补充补充例例3 创建一个规则，并绑定到创建一个规则，并绑定到C表的表的C

31、No列，用于列，用于限定限定CNo的输入范围。的输入范围。CREATE RULE Cno_rule AS range like 1-50-90-9 EXEC sp_bindrule Cno_rule,C.CNo An Introduction to Database System62(2)用存储过程用存储过程sp_unbindrule解除规则的绑定解除规则的绑定 sp_unbindrule objname = object_name,futureonly 例例4 解除已绑定到解除已绑定到S表表Age属性的规则。属性的规则。 EXEC sp_unbindrule S.Age 补充补充2.规则的绑

32、定与松绑规则的绑定与松绑An Introduction to Database System63提醒提醒：在删除一个规则前必须先将与其绑定的：在删除一个规则前必须先将与其绑定的 对象松绑。对象松绑。 3.删除规则删除规则 n 使用使用DROP RULE命令删除规则命令删除规则 DROP RULE rule_name ,.n 例例5 删除删除age_rule规则。规则。 DROP RULE age_rulen 使用使用SSMS删除规则删除规则 补充补充-规则规则 An Introduction to Database System64补充补充-默认默认 n 默认默认（DefaultDefault

33、）: : 是向用户输入记录时没有指定具体数据的是向用户输入记录时没有指定具体数据的列中自动插入的数据列中自动插入的数据。 n 说明说明： 表的一列或一个用户自定义数据类型只能表的一列或一个用户自定义数据类型只能与一个默认绑定。与一个默认绑定。An Introduction to Database System65补充补充-默认默认 1.1.创建默认创建默认 CREATE DEFAULT CREATE DEFAULT 默认名默认名 AS AS 默认表达式默认表达式例例5-17创建出生日期默认创建出生日期默认birthday_defa。 CREATE DEFAULT birthday_defa AS 1992-1-12.2.查看默认查看默认 sp_helptext sp_helptext 默认名默认名An Introduction to Database System663.3.默认的绑定与松绑默认的绑定与松绑 (1)用存储过程用存储过程sp_bindefault 绑定默认绑定默认 sp_bindefault defname = default,objname = object_name , futureonly (2)用存储过程用存储过程sp_unbindefault 解除默认的绑定解除默认的绑定 sp_unbindefault objname =object_name ,