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

AnIntroductiontoDatabaseSystem1数据库系统概论AnIntroductiontoDatabaseSystem第五章数据库完整性AnIntroductiontoDatabaseSystem2第五章数据库完整性什么是数据库的完整性?数据的正确性和相容性防止不合语义的、不正确的数据进入数据库。例:学生的年龄必须是整数，取值在6-50间；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校已有的系；完整性：保证数据库能否真实地反映现实世界.区别数据完整性和安全性的两个概念。AnIntroductiontoDatabaseSystem3

完整性约束条件是数据模型的组成部分，约束数据库中数据的语义。

检查用户发出的操作请求是否违背了完整性约束条件。

操作违背了完整性约束条件，则采取一定的操作来保证数据的完整性。为保证完整性，DBMS必须做到：1.完整性约束的定义机制2.完整性的检查机制3.违约反应第五章数据库完整性AnIntroductiontoDatabaseSystem4第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义完整性5.4完整性约束命名子句5.5触发器AnIntroductiontoDatabaseSystem55.1实体完整性1.实体完整性定义在CREATETABLE语句中用PRIMARYKEY定义AnIntroductiontoDatabaseSystem65.1实体完整性[例5.1]在数据库中，定义Student表的Sno属性为主码。定义在列级约束：

CREATETABLEStudent(SnoCHAR(8),SnameCHAR(8),SageINT);

定义在表级约束：

CREATETABLEStudent(SnoCHAR(8)，

SnameCHAR(8),SageINT，

);PRIMARYKEY,PRIMARYKEY(Sno));AnIntroductiontoDatabaseSystem75.1实体完整性[例5.2]在SC表中定义(Sno,Cno)为主码。

CREATETABLESC(SnoCHAR(5),CnoCHAR(2),GradeSMALLINT,

PRIMARYKEY(Sno,Cno));AnIntroductiontoDatabaseSystem85.1实体完整性1.实体完整性定义在CREATETABLE语句中用PRIMARYKEY定义

码由单字段构成：

定义可以为列级或表级约束；

码由多字段构成：

定义只能为表级约束；AnIntroductiontoDatabaseSystem95.1实体完整性2.实体完整性检查和违约处理

(1)完整性检查

当用户插入记录或修改主码时，DBMS自动检查：每个主属性值是否为空；主码值是否惟一。

(2)违约处理拒绝操作。AnIntroductiontoDatabaseSystem105.1实体完整性2.实体完整性检查和违约处理

(3)检查主码是否惟一的方法

全表扫描判断新的主码值在表中是否已存在。缺点：效率低利用主码的B＋索引优点：效率高AnIntroductiontoDatabaseSystem115.1实体完整性keyF1F2F33………25………20………15………93………79………65………84………71………51………7………30………41………54………65………69………68………25511230667837152025304151546568697176798493AnIntroductiontoDatabaseSystem12第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义完整性5.4完整性约束命名子句5.5触发器AnIntroductiontoDatabaseSystem135.2参照完整性1.参照完整性定义

在CREATETABLE语句中：

用FOREIGNKEY短语定义外码列；用REFERENCES短语指明外码参照哪个表的主码。AnIntroductiontoDatabaseSystem145.2参照完整性[例5.3]在SC表中的参照完整性。

CREATETABLESC(SnoCHAR(8)NOTNULL,CnoCHAR(3)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Cno)REFERENCEScourse(Cno)

);REFERENCESstudent(Sno),AnIntroductiontoDatabaseSystem155.2参照完整性[例]在SC表中的参照完整性。

ALTERTABLESC

ADDFOREIGNKEY(SNO)REFERENCESSTUDENT(SNO)AnIntroductiontoDatabaseSystem162.参照完整性检查和违约处理对被参照表和参照表进行增、删、改操作时进行检查有四种情况可能破坏参照完整性：参照表中插入新元组；修改外码值；被参照表中删除元组；修改主码值。5.2参照完整性AnIntroductiontoDatabaseSystem17

当参照完整性不一致时，系统采取以下策略：拒绝执行（默认策略）

级联操作（CASCADE）当删除或修改被参照表的一个元组造成与参照表不一致时，则删除或修改参照表中所有不一致的元组。设置为空（SET-NULL）当删除或修改被参照表的一个元组造成与参照表不一致时，则将参照表中所有造成不一致的元组对应属性置空。5.2参照完整性

定义参照完整性时，除了定义外码，还应定义外码是否级联、是否为空。AnIntroductiontoDatabaseSystem185.2参照完整性[例5.4]在SC表中的参照完整性。

CREATETABLESC(SnoCHAR(8)NOTNULL,CnoCHAR(3)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Cno)REFERENCEScourse(Cno)

ONDELETECASCADEONUPDATECASCADE);REFERENCESstudent(Sno),AnIntroductiontoDatabaseSystem19例：要删除Student关系中Sno=2012215121的元组，而SC关系中有5个元组的Sno都等于201215121。置空值删除：

将SC关系中所有Sno=201215121的元组Sno置空。√×√级联删除：

将SC关系中所有5个Sno=201215121的元组一起删除。依此类推可以继续级联下去。

受限删除：

系统将拒绝执行此删除操作。5.2参照完整性AnIntroductiontoDatabaseSystem20第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义完整性5.4完整性约束命名子句5.5触发器AnIntroductiontoDatabaseSystem215.3用户定义的完整性1.属性上约束条件的定义

使用CREATETABLE语句在定义属性的同时，定义对属性值的限制。

包括：列值非空(NOTNULL)

列值唯一(UNIQUE)

检查列值是否满足一个布尔表达式(CHECK子句)。AnIntroductiontoDatabaseSystem225.3用户定义的完整性(1)不允许取空值[例5.5]在定义SC表时，说明Sno、Cno属性不取空

CREATETABLESC(SnoCHAR(5)NOTNULL,CnoCHAR(2)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno));AnIntroductiontoDatabaseSystem235.3用户定义的完整性(2)列值惟一[例5.6]建立部门表Dept，要求部门名称Dname惟一，部门编号为主码。

CREATETABLEDept(DnoNUMERIC(2)NOTNULL,DnameCHAR(20)UNIQUE,LocationCHAR(10),PRIMARYKEY(Dno));AnIntroductiontoDatabaseSystem245.3用户定义的完整性(2)列值惟一[例]若部门表Dept已建立，添加部门名称Dname惟一的约束。

ALTERTABLEDeptADDUNIQUE(Dname);AnIntroductiontoDatabaseSystem255.3用户定义的完整性[例5.7]Student表中Ssex只允许取“男”和“女”。CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY,SnameCHAR(8)NOTNULL,SsexCHAR(2)CHECK(SsexIN(‘男’,’女’)),SageSMALLINT,SdeptCHAR(20));(3)用CHECK短语指定列值应该满足的条件AnIntroductiontoDatabaseSystem265.3用户定义的完整性[例]向Student表添加Ssex只许取“男”和“女”的约束。ALTERTABLEStudentADDCHECK(SsexIN(‘男’,’女’));(3)用CHECK短语指定列值应该满足的条件AnIntroductiontoDatabaseSystem275.3用户定义的完整性[例5.8]在定义SC表时，说明Grade应该在0和100之间。

CREATETABLESC(SnoCHAR(5)NOTNULL,CnoCHAR(2)NOTNULL,GradeSMALLINTCHECK(Grade>=0ANDGrade<=100),PRIMARYKEY(Sno,Cno)

……

);

(3)用CHECK短语指定列值应该满足的条件AnIntroductiontoDatabaseSystem285.3用户定义的完整性2.属性上约束条件检查和违约处理

当往表中插入元组或修改属性的值时，RDBMS就检查属性上的约束条件是否被满足，如果不满足操作被拒绝执行。AnIntroductiontoDatabaseSystem295.3用户定义的完整性3.元组上的约束条件的定义

在CREATETABLE语句可以用CHECK短语定义元组上的约束条件，即元组级限制元组级限制可以设置不同属性之间的取值的相互约束条件。AnIntroductiontoDatabaseSystem305.3用户定义的完整性3.元组上的约束条件的定义

在CREATETABLE语句可以用CHECK短语定义元组上的约束条件，即元组级限制元组级限制可以设置不同属性之间的取值的相互约束条件。4.元组上的约束条件检查和违约处理

例：当学生的性别是男时，姓名中不能包含“花”

AnIntroductiontoDatabaseSystem31第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义完整性5.4完整性约束命名子句5.5触发器AnIntroductiontoDatabaseSystem325.4完整性约束命名子句

SQL在提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件进行命名。1.完整性约束命名子句格式：

CONSTRAINT<约束命名>[PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语]

放置在任何一个约束短语的前面。AnIntroductiontoDatabaseSystem33[例5.10]建立学生表student，姓名不能取空值，年龄小于30，性别只能取“男”或“女”。

CREATETABLEStudent

(SnoNUMERIC(6)

SnameCHAR(8)

NOTNULL,

SsexCHAR(2)CHECK(SsexIN(‘男’,’女’)),

SageINTCHECK(Sage<30),PRIMARYKEY(Sno));

CONSTRAINTC1

CONSTRAINT

C2

CONSTRAINTC3

CONSTRAINT

StuKey

AnIntroductiontoDatabaseSystem345.4完整性约束命名子句

2.修改表中的完整性限制

(1)删除完整性限制格式：ALTERTABLEStudent

DROP[CONSTRAINT]<约束命名>[例5.12]去掉[例10]student表中对性别的限制。

ALTERTABLEStudentDROP[CONSTRAINT]C3;(2)修改完整性限制

先删除，再添加。AnIntroductiontoDatabaseSystem35第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义完整性5.4完整性约束命名子句5.5触发器AnIntroductiontoDatabaseSystem365.5触发器1.触发器

(1)什么是触发器？

它是定义在表上的一类由事件驱动的特殊过程作用：

用来实现当使用update、insert、delete对表进行操作时，自动触发的处理方法；实现复杂的业务要求，类似于约束保证数据的一致性。AnIntroductiontoDatabaseSystem375.5触发器1.触发器

(2)触发器的分类

After触发器：（默认类型）

触发Insert、Update、Delete动作之后执行。（表中数据已经变动之后）

Insteadof触发器：

当执行Insert、Update、Delete操作时用触发器中的操作来代替该操作。

(表中数据改动之前)AnIntroductiontoDatabaseSystem385.5触发器2.定义触发器格式：(P169)

CreateTrigger<触发器名>on<对象名>{for|after|insteadof}{[delete],[insert],[update]}

as<触发器的条件和操作>触发器的类型触发事件AnIntroductiontoDatabaseSystem395.5触发器[例1]创建一个向学生表添加新记录时显示欢迎信息的触发器。

CreateTriggerT_InOnstudentafterinsertasprint‘欢迎新同学的到来！'

AnIntroductiontoDatabaseSystem405.5触发器3.建立触发器的条件

CREATETRIGGER语句必须是批处理语句中的第一条；只有表的拥有者可以创建触发器；一个表只能创建一定数量的触发器；在同一模式下，触发器名必须惟一；当触发器被激活时，只有触发条件为真时触发体才被执行。AnIntroductiontoDatabaseSystem415.5触发器4.触发器工作原理

(1)触发器工作时建立两个表：

inserted表：

存储insert和update语句所影响的数据行副本

inserted中的行是触发器表中新行的副本。

deleted表：

存储delete和update语句所影响的数据行副本

deleted中的行是从触发器表中删除的行。

触发器作用的表和deleted表没有相同的行AnIntroductiontoDatabaseSystem425.5触发器4.触发器工作原理

(2)这两个表的特点：

系统自动维护对用户而言，是只读的表结构与触发器作用的表结构相同与触发器同时存在（触发器运行结束，系统自动删除这两表）AnIntroductiontoDatabaseSystem435.5触发器4.触发器工作原理

(3)各种操作时触发器的工作：

insert操作

insert触发器被触发激活

新记录会添加到基本表和inserted表。

delete操作

delete触发器被触发激活

被删除的记录会存放到deleted表中。

update操作

相当于删除一条旧记录,插入一条新纪录。AnIntroductiontoDatabaseSystem445.5触发器[例2]创建一个限制年龄不能小于10的触发器

CREATETRIGGERt_ageonStudentFORInsert,UpdateASIF()<10BEGINPRINT'年龄不能小于10，操作失败'

ROLLBACK

END;selectsagefrominsertedAnIntroductiontoDatabaseSystem455.5触发器[例3]为student表建立一个触发器t_del，禁止删除学号为201215122的学生createtriggert_delonstudentforupdate,deleteasif((selectsnofromdeleted)=‘201215122')

beginprint'不允许删除该学生，操作失败！'rollbackendAnIntroductiontoDatabaseSystem465.5触发器[例4]为student表建立一个触发器t_sno，要求在更新操作中不能修改学号。

CREATETRIGGERt3ONStudentFORUpdateASIFUpdate(sno)BEGINPRINT'学号不允许修改！'ROLLBACKEND

AnIntroductiontoDatabaseSystem475.5触发器[例5]建立触发器t_sc，要求修改SC表的成绩时，同时将记录插入的old_sc。createtableold_sc(snovarchar(12),cnochar(4),gradeint,unamevarchar(10),udatedatetimedefaultgetdate())AnIntroductiontoDatabaseSystem48CREATETRIGGERupdate_scONscFORUPDATEAS

ifupdate(grade)

begin

insertintoold_sc(sno,cno,grade)

select*fromdeleted

updateold_sc

setuname=user

wheresno=(selectsnofromdeleted)

andcno=(selectcnofromdeleted)

endAnIntroductiontoDatabaseSystem495.5触发器[例6]创建V1视图，包括如下四个属性，解决不能利用该视图插入数据的问题。

createviewv1

as

selectStudent.sno,sname,cno,grade

fromstudent,sc

wherestudent.sno=sc.snoAnIntroductiontoDatabaseSystem50createtriggert_ins_v1onv1insteadofINSERTasbeginINSERTINTOStudent(sno,sname)

SELECTsno,snamefrominserted

INSERTINTOSC

SELECTsno,cno,gradefrominserted

endAnIntroductiontoDatabaseSystem515.5触发器5.激活触发器的过程

(1)执行insteadof触发器

(2)执行SQL语句

(3)执行after触发器6.删除管理

droptrigger<触发器名>on<表名>

AnIntroductiontoDatabaseSystem525.5触发器7.触发器和约束的比较

相同：触发器和约束都可用于处理数据的输入

不同：

(1)约束：表中所有的数据均满足

(2)触发器

创建触发器前表中的数据可不满足实施瞬时约束，即数据改变时约束AnIntroductiontoDatabaseSystem53分析该触发器的作用createtriggert5onscforinsert,updateasifupdate(grade)updatestudentsetssum=(selectsum(ccredit)oin(ofromsc,insertedwheresc.sno=inserted.snoandsc.grade>=60))wheresno=(selectsnofrominserted)

某工厂的仓库管理数据库的部分关系模式如下：仓库(仓库号,面积,负责人,电话)原材料(编号,名称,数量,储备量,仓库号)

订购计划(原材料编号,订购数量)要求触发器实现：当仓库中的任一原材料的数量小于其储备量时，向订购计划表中插入该原材料的订购计划，其订购量为储备量的3倍练习仓库(仓库号,面积,负责人,电话)原材料(编号,名称,数量,储备量,仓库号)

订购计划(原材料编号,订购数量)

CREATETRIGGERt_订购

ON原材料FOR

。

AS

IF

<

)Insertinto订购计划VALUES(

，

)

Insert,Update(select编号frominserted)(select数量

frominserted)(select储备量

frominserted)(select储备量*

3

frominserted)CREATETRIGGERt_订购ONscFORInsert,UpdateASIF(select储备量frominserted)<(select数量frominserted)begin

ifexists(select原材料编号from订购计划

where原材料编号=(select编号frominserted))print‘已存在该材料的订购计划！'elseInsertinto订购计划

VALUES((select编号frominserted),(select储备量*3frominserted))end避免重复添加订购计划的触发器AnIntroductiontoDatabaseSystem57补充—规则规则（Rule）是数据库对存储在表中的列或用户自定义数据类型的取值规定和限制。特点①规则与其作用的对象是相互独立的，即对象的删除、修改不会对与之相连的规则产生影响；②规则与CHECK类似，与约束可同时使用；③一列可以有一个规则及多个约束；④规则可作用于自定义数据类型,但约束不能。AnIntroductiontoDatabaseSystem58补充—规则1.创建规则CREATERULE规则名AS约束条件

[例1]

创建学生年龄在18~60岁的规则。

CREATERULEage_ruleAS@age>=18and@age<=60局部变量Where中的任何表达式AnIntroductiontoDatabaseSystem59补充—规则2.规则的绑定与松绑若要规则发生作用，需将其与数据库表或用户自定义对象联系起来。绑定：指定规则作用于哪个表的哪一列或哪个用户自定义数据类型。松绑：解除规则与对象的绑定。AnIntroductiontoDatabaseSystem60补充2.规则的绑定与松绑(1)用存储过程sp_bindrule绑定规则

sp_bindrule[@rulename=]规则名,[@objname=]对象名[,‘futureonly’]

[例2]

绑定规则age_rule到Student表Sage属性EXECsp_bindruleage_rule,'Student.Sage'只在自定义数据类型时用AnIntroductiontoDatabaseSystem61补充[例3]

创建一个规则，并绑定到C表的CNo列，用于限定CNo的输入范围。CREATERULECno_ruleAS@rangelike'[1-5][0-9][0-9]'EXECsp_bindrule'Cno_rule',C.CNoAnIntroductiontoDatabaseSystem62(2)用存储过程sp_unbindrule解除规则的绑定

sp_unbindrule[@objname=]'object_name' [,'futureonly'][例4]

解除已绑定到S表Age属性的规则。

EXECsp_unbindrule'S.Age'补充2.规则的绑定与松绑AnIntroductiontoDatabaseSystem63提醒：在删除一个规则前必须先将与其绑定的对象松绑。3.删除规则

使用DROPRULE命令删除规则

DROPRULE{rule_name}[,...n]

[例5]

删除ag