华中科技大学数据库课件第05章-数据库完整性优秀PPT.ppt-淘文阁

资源描述

《华中科技大学数据库课件第05章-数据库完整性优秀PPT.ppt》由会员分享，可在线阅读，更多相关《华中科技大学数据库课件第05章-数据库完整性优秀PPT.ppt（87页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、 2011 by1数据库的完整性是指数据的正确性、有数据库的完整性是指数据的正确性、有效性和相容性。即数据库中数据与现实效性和相容性。即数据库中数据与现实世界的实际状况是相符合的或数据库中世界的实际状况是相符合的或数据库中数据自身不存在自相冲突的现象。数据自身不存在自相冲突的现象。第第5章章数据库完整性数据库完整性An Introduction to Database System 2011 by2完整性完整性 vs 平安性平安性完整性完整性防止不符语义的数据，错误的输入防止不符语义的数据，错误的输入防范的对象：不合语义的数据防范的对象：不合语义的数据平安性平安性防止恶意破坏和非法存取防止恶

2、意破坏和非法存取防范对象：非法的用户和非法操作。防范对象：非法的用户和非法操作。An Introduction to Database System 2011 by3完整性限制机制完整性限制机制定义完整性约束条件定义完整性约束条件实施完整性检查实施完整性检查如有违反，作何种处置如有违反，作何种处置(拒绝、报告、订拒绝、报告、订正、忽视正、忽视)An Introduction to Database System 2011 by45.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名子句完整性约束命名子句5.5 域中的完整性限制域中

3、的完整性限制5.6 触发器触发器5.7 小结小结第第5章章数据库完整性数据库完整性An Introduction to Database System 2011 by55.1 实体完整性实体完整性5.1.1 实体完整性定义实体完整性定义5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理An Introduction to Database System 2011 by65.1.1 实体完整性定义实体完整性定义关系模型的实体完整性关系模型的实体完整性CREATE TABLE中用中用PRIMARY KEY定义定义单属性构成的码有两种说明方法单属性构成的码有两种说明方法定义为列级约束条

4、件定义为列级约束条件定义为表级约束条件定义为表级约束条件对多个属性构成的码只有一种说明方法对多个属性构成的码只有一种说明方法定义为表级约束条件定义为表级约束条件 An Introduction to Database System 2011 by75.1.1 实体完整性定义实体完整性定义create table 表名表名(|,.n ):=列名列名数据类型数据类型 DEFAULT 常量表达式常量表达式|IDENTITY (种子种子,增量增量).n An Introduction to Database System 2011 by85.1.1 实体完整性定义实体完整性定义:=CONSTRAIN

6、,.n )|FOREIGN KEY (列名,.n )REFERENCES 被引用表 (被引用列,.n )ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION|CHECK (逻辑表达式)An Introduction to Database System 2011 by105.1.1 实体完整性定义实体完整性定义例1 将Student表中的Sno属性定义为码 (1)在列级定义主码 CREATE TABLE Student (Sno CHAR(9)PRIMARY KEY，Sname CHAR(20)NOT NULL，Ssex CHAR(2)

7、，Sage SMALLINT，Sdept CHAR(20)An Introduction to Database System 2011 by115.1.1 实体完整性定义实体完整性定义可以显式给Primary Key约束命名 CREATE TABLE Student (Sno CHAR(9)constraint PK_Student PRIMARY KEY,Sname CHAR(20)NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20)An Introduction to Database System 2011 by125.1.1 实体完整性

8、定义实体完整性定义(2)在表一级定义Primary Key约束 CREATE TABLE Student (Sno CHAR(9),Sname CHAR(20)NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20),constraint PK_Student Primary Key(sno)An Introduction to Database System 2011 by135.1.1 实体完整性定义实体完整性定义例2将SC表中的Sno，Cno属性组定义为码 CREATE TABLE SC(Sno CHAR(9)NOT NULL，Cno CHAR

9、(4)NOT NULL，Grade SMALLINT，PRIMARY KEY(Sno，Cno)/*只能在表级定义主码*/)An Introduction to Database System 2011 by145.1.1 实体完整性定义实体完整性定义关系模型完整性的修改关系模型完整性的修改Alter Table ADD(添加新约束）添加新约束）Alter Table Drop(删除约束删除约束)修改现有约束？修改现有约束？An Introduction to Database System 2011 by155.1.1 实体完整性定义实体完整性定义ALTER TABLE 表名 ALTER CO

12、Y (列名,.n )REFERENCES 被引用表 (被引用列,.n )ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION|DEFAULT 常量表达式 FOR 列名|CHECK (逻辑表达式)An Introduction to Database System 2011 by195.1.1 实体完整性定义实体完整性定义例3 建SC1表时，主码定义错误，修改之。这是当时定义的语句：CREATE TABLE SC1 (Sno CHAR(9)constraint PK_SC1 PRIMARY KEY，Cno char(4)，Grade sm

13、allint)An Introduction to Database System 2011 by205.1.1 实体完整性定义实体完整性定义(1)删除原约束(2)定义新约束alter table sc1 drop constraint pk_sc1alter table sc1 alter column cno char(4)not nullalter table sc1 add constraint pk_sc1 primary key(sno,cno)An Introduction to Database System 2011 by215.1.1 实体完整性定义实体完整性定义关系模型完

14、整性的修改关系模型完整性的修改Alter Table ADD(添加新约束）添加新约束）Alter Table Drop(删除约束删除约束)修改现有约束？修改现有约束？An Introduction to Database System 2011 by225.1 实体完整性实体完整性5.1.1 实体完整性定义实体完整性定义5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理An Introduction to Database System 2011 by235.1.2 实体完整性检查和违约处理实体完整性检查和违约处理何时检查何时检查插入插入:insert更新主码列更新主码列:updat

15、e 检查内容检查内容检查主码值是否唯一，假如不唯一则拒绝插入或修检查主码值是否唯一，假如不唯一则拒绝插入或修改改检查主码的各个属性是否为空，只要有一个为空就检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改拒绝插入或修改An Introduction to Database System 2011 by245.1.2 实体完整性检查和违约处理实体完整性检查和违约处理检查方法一检查方法一:全表扫描全表扫描（耗时耗时）对策对策:在主码上自动建索引在主码上自动建索引An Introduction to Database System 2011 by255.1 实体完整性实体完整性5.2 参照

16、完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名子句完整性约束命名子句5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结第第5章章数据库完整性数据库完整性An Introduction to Database System 2011 by265.2 参照完整性参照完整性5.2.1 参照完整性定义参照完整性定义5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理An Introduction to Database System 2011 by275.2.1 参照完整性定义参照完整性定义关系模型的参照完整性定义关系模型的参照完整性

17、定义CREATE TABLE和和ALTER TABLE用用FOREIGN KEY短语定义哪些列为外码短语定义哪些列为外码用用REFERENCES短语指明这些外码参照哪些表的短语指明这些外码参照哪些表的主码主码 An Introduction to Database System 2011 by285.2.1 参照完整性定义参照完整性定义create table 表名表名(|,.n ):=列名列名数据类型数据类型 DEFAULT 常量表达式常量表达式|IDENTITY (种子种子,增量增量).n An Introduction to Database System 2011 by295.2.1

19、|NONCLUSTERED (列名 ASC|DESC ,.n )|FOREIGN KEY (列名,.n )REFERENCES 被引用表 (被引用列,.n )ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION|CHECK (逻辑表达式)An Introduction to Database System 2011 by315.2.1 参照完整性定义参照完整性定义例3 定义SC(sno,cno,grade)中的参照完整性 CREATE TABLE SC(Sno CHAR(9)NOT NULL，Cno CHAR(4)NOT NULL，Gr

20、ade SMALLINT，PRIMARY KEY(Sno，Cno)，/*在表级定义实体完整性*/FOREIGN KEY(Sno)REFERENCES Student(Sno)，/*在表级定义参照完整性*/FOREIGN KEY(Cno)REFERENCES Course(Cno)/*在表级定义参照完整性*/)这里，两个foreign key都没有显式取名An Introduction to Database System 2011 by325.2.1 参照完整性定义参照完整性定义还可以写成：CREATE TABLE SC(Sno CHAR(9)REFERENCES Student(Sno)，C

21、no CHAR(4)REFERENCES Course(Cno)，Grade SMALLINT，PRIMARY KEY(Sno，Cno)这似乎是最“经济”的写法了，不仅省略了constraint，还省略了foreign key An Introduction to Database System 2011 by335.2.1 参照完整性定义参照完整性定义例4 假使当时定义SC(sno,cno,grade)时，遗漏了参照完整性定义，现需补上，该怎么做？以下是当时的语句：CREATE TABLE SC(Sno CHAR(9)，Cno CHAR(4)，Grade SMALLINT，PRIMARY K

22、EY(Sno，Cno)，/*在表级定义实体完整性*/)An Introduction to Database System 2011 by345.2.1 参照完整性定义参照完整性定义【例4】假使当时定义SC(sno,cno,grade)时，遗漏了参照完整性定义，现需补上，该怎么做？以下是当时的语句：CREATE TABLE SC(Sno CHAR(9)，Cno CHAR(4)，Grade SMALLINT，PRIMARY KEY(Sno，Cno)，/*在表级定义实体完整性*/)还是先回忆一下Alter table的用法吧An Introduction to Database System 20

23、11 by355.2.1 参照完整性定义参照完整性定义ALTER TABLE 表名 ALTER COLUMN 列名数据类型 (精度,小数位数)NULL|NOT NULL|ADD ,.n|ADD ,.n|DROP CONSTRAINT 约束名|COLUMN 列名 ,.n An Introduction to Database System 2011 by365.2.1 参照完整性定义参照完整性定义:=列名列名数据类型数据类型 DEFAULT 常量表达式常量表达式|IDENTITY (种子种子,增量增量).n An Introduction to Database System 2011 by

26、nces s(sno),constraint FK_C_SC foreign key(cno)references c(cno)An Introduction to Database System 2011 by405.2 参照完整性参照完整性5.2.1 参照完整性定义参照完整性定义5.2.2 参照完整性检查和违约处理参照完整性检查和违约处理An Introduction to Database System 2011 by415.2.2 参照完整性检查和违约处理参照完整性检查和违约处理可能破坏参照完整性的状况及违约处理被参照表被参照表（例如（例如Student）参照表参照表（例如（例如SC）

27、违约处理违约处理可能破坏参照完整性可能破坏参照完整性插入元组插入元组拒绝拒绝可能破坏参照完整性可能破坏参照完整性修改外码值修改外码值拒绝拒绝删除元组删除元组可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级连删除级连删除/设置为空设置为空值值修改主码值修改主码值可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级连修改级连修改/设置为空设置为空值值An Introduction to Database System 2011 by425.2.2 参照完整性检查和违约处理参照完整性检查和违约处理参照完整性违约处理参照完整性违约处理1.拒绝拒绝(NO ACTION)执行执行默认策略默认策略2.级

28、联级联(CASCADE)操作操作3.设置为空值（设置为空值（SET-NULL）对于参照完整性，除了应当定义外码，还应定对于参照完整性，除了应当定义外码，还应定义外码列是否允许空值义外码列是否允许空值An Introduction to Database System 2011 by435.2.2 参照完整性检查和违约处理参照完整性检查和违约处理【例【例5】删除例】删除例4定义的参照完整性，重新定义完整性，要求实现级联更新定义的参照完整性，重新定义完整性，要求实现级联更新和级联删除。和级联删除。alter table sc drop FK_S_SC,FK_C_SCalter table sc a

29、dd constraint FK_S_SC foreign key(sno)references s(sno)on delete cascade on update cascade,constraint FK_C_SC foreign key(cno)references c(cno)on delete cascade on update cascadeAn Introduction to Database System 2011 by445.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名子句完整性约束命名子句5.5 域中的完

30、整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结第第5章章数据库完整性数据库完整性An Introduction to Database System 2011 by455.3 用户定义的完整性用户定义的完整性常见的自定义完整性常见的自定义完整性列值缺省列值缺省(DEFAULT)列值非空列值非空(NOT NULL)列值唯一列值唯一(UNIQUE)列值满足列值满足boolean表达式表达式(CHECK)NOT NULL和和UNIQUE只出现在列约束上只出现在列约束上DEFAULT和和CHECK约束既可以出现在列约束既可以出现在列约束上，也可以出现在表约束上约束上，也可以出现在表约束

33、工号,产品号,数量,单价,销售日期)”，其中工号限定4位数字，数量须为正数，销售日期默认为当日。create table 销售明细表(流水号 bigint primary key,工号 char(4),产品号 char(4),数量 int,单价 money,销售日期 datetime default getdate(),constraint CK_工号 check(工号 like 0-90-90-90-9),constraint CK_数量 check(数量 0)An Introduction to Database System 2011 by495.3 用户定义的完整性用户定义的完整性【例

34、7】给表student的ssex列添加约束,该列的值只能取”男”或”女”。alter table student add constraint CK_ssex check(ssex in(男,女)An Introduction to Database System 2011 by505.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名子句完整性约束命名子句5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结第第5章章数据库完整性数据库完整性An Introduction to Database Syst

35、em 2011 by515.4 完整性约束命名子句完整性约束命名子句全部的完整性约束都可以显式命名。:=CONSTRAINT 约束名NULL或|NOT NULL约束|PRIMARY KEY或 UNIQUE 约束|FOREIGN KEY 约束|CHECK 约束 An Introduction to Database System 2011 by525.1 实体完整性实体完整性5.2 参照完整性参照完整性5.3 用户定义的完整性用户定义的完整性5.4 完整性约束命名子句完整性约束命名子句5.5 域中的完整性限制域中的完整性限制5.6 触发器触发器5.7 小结小结第第5章章数据库完整性数据库完整性

36、An Introduction to Database System 2011 by535.6 触发器触发器问题的提出问题的提出-如何实现如何实现?职工职工(工号工号,姓名姓名,职称职称,工资工资),约束约束:(1)职称职称 in (助教助教,讲师讲师,副教授副教授,教教授授)(2)工资工资助教工资助教工资 1600-1800讲师讲师 1800-2200 副教授副教授2200-2800 教授教授2800-6000(3)工资修改只能往上调工资修改只能往上调,不能下调不能下调,且每次调幅且每次调幅不大于不大于1000An Introduction to Database System 2011

37、by545.6 触发器触发器SQL Server供应两种主机制来强制业务规则和供应两种主机制来强制业务规则和数据完整性数据完整性约束约束触发器触发器触发器是用户定义在表上的一类由事务驱动的触发器是用户定义在表上的一类由事务驱动的特殊过程特殊过程,一旦定义一旦定义,则对表中数据进行则对表中数据进行insert,delete或或update操作时自动触发执行操作时自动触发执行An Introduction to Database System 2011 by555.6 触发器触发器触发器可以包含困难的语句和流程限制触发器可以包含困难的语句和流程限制,其主要优点是用户可以用编程的方法来其主要优点是用

38、户可以用编程的方法来实现困难的处理逻辑和商业规则实现困难的处理逻辑和商业规则触发器和触发它的语句构成一个可在触触发器和触发它的语句构成一个可在触发器内回滚的事务发器内回滚的事务An Introduction to Database System 2011 by565.6 触发器触发器触发器的优点触发器的优点完成比完成比CHECK更困难的数据约束更困难的数据约束.触发触发器可以引用其他表中的列，还可以插入、器可以引用其他表中的列，还可以插入、更新或删除别的表中的数据更新或删除别的表中的数据触发器也可以评估数据修改前后的表状触发器也可以评估数据修改前后的表状态态,并依据其差异实行对策并依据其差异

39、实行对策触发器可以支持约束的全部功能；但它触发器可以支持约束的全部功能；但它在所给出的功能上并不总是最好的方法在所给出的功能上并不总是最好的方法An Introduction to Database System 2011 by575.6 触发器触发器5.6.1 限制流语句限制流语句5.6.2 事务事务5.6.3 定义触发器定义触发器5.6.4 激活触发器激活触发器5.6.5 修改和删除触发器修改和删除触发器An Introduction to Database System 2011 by585.6.1 限制流语句限制流语句1 变量的说明与赋值变量的说明与赋值DECLARE 变量名变量名数

40、据类型数据类型 ,.nSET|SELECT 局部变量名局部变量名=表达式表达式2 BEGIN END语句语句BEGIN SQL语句语句|语句块语句块 ENDAn Introduction to Database System 2011 by595.6.1 限制流语句限制流语句3 IF.ELSE语句语句IF 布尔表达式布尔表达式 SQL语句语句|语句块语句块 ELSE SQL语句语句|语句块语句块 An Introduction to Database System 2011 by605.6.1 限制流语句限制流语句4 WHILE语句语句WHILE 布尔表达式布尔表达式 SQL语句语句|语句块语

41、句块 5 CASE表达式表达式CASE 测试表达式测试表达式 WHEN 简洁表达式简洁表达式 THEN 结果表达式结果表达式 .n ELSE 结果表达式结果表达式ENDAn Introduction to Database System 2011 by615.6.1 限制流语句限制流语句5 CASE表达式表达式(续续)CASE WHEN 布尔表达式布尔表达式 THEN 结果表达式结果表达式 .n ELSE 结果表达式结果表达式ENDAn Introduction to Database System 2011 by625.6.1 限制流语句限制流语句【例【例8】列出列出student表中全部学

42、生的学号，姓表中全部学生的学号，姓名和性别将性别分别译成名和性别将性别分别译成male和和femaleSELECT sno no,sname name,(CASE ssex WHEN 男 THEN male WHEN 女 THEN female END)gender FROM student上述Case子句还可以写成:case when ssex=男 then male when ssex=女 then female endAn Introduction to Database System 2011 by635.6 触发器触发器5.6.1 限制流语句限制流语句5.6.2 事务事务5.6.3

43、定义触发器定义触发器5.6.4 激活触发器激活触发器5.6.5 修改和删除触发器修改和删除触发器An Introduction to Database System 2011 by645.6.2 事务事务什么是事务什么是事务由用户定义的一组操作指令由用户定义的一组操作指令(SQL语句语句)序序列列这组指令作为一个不行分割的整体，完这组指令作为一个不行分割的整体，完成某件逻辑工作成某件逻辑工作这组指令要么全部执行，要么全不执行这组指令要么全部执行，要么全不执行例如例如:在存款在存款(账户账户,余额余额)中，从账户中，从账户A向向账户转帐金额账户转帐金额N(A)原子性原子性(C)一样性一样性(I)

44、隔离性隔离性(D)长久长久性性An Introduction to Database System 2011 by655.6.2 事务事务事务的定义事务的定义Begin transaction 语句块语句块commit|rollbackCommit:正常结束正常结束Rollback:异样结束异样结束(需回滚需回滚)An Introduction to Database System 2011 by665.6 触发器触发器5.6.1 限制流语句限制流语句5.6.2 事务事务5.6.3 定义触发器定义触发器5.6.4 激活触发器激活触发器5.6.5 修改和删除触发器修改和删除触发器An Intro

45、duction to Database System 2011 by675.6.3 定义触发器定义触发器在设计触发器时，在设计触发器时，Microsoft SQL Server 2000 供应了两种选项：供应了两种选项：INSTEAD OF触发器。它指定执行触发器的内容而不触发器。它指定执行触发器的内容而不是执行引发触发器执行的是执行引发触发器执行的SQL语句。一个表只能定语句。一个表只能定义一个义一个INSTEAD OF触发器。触发器。INSTEAD OF触发器触发器可以定义在视图上。可以定义在视图上。AFTER触发器。在执行完触发器。在执行完INSERT、UPDATE或或DELETE等操作

46、并处理完约束之后执行等操作并处理完约束之后执行AFTER触发触发器。器。AFTER 触发器只能在表上指定。触发器只能在表上指定。An Introduction to Database System 2011 by685.6.3 定义触发器定义触发器触发器、约束共存时执行依次：触发器、约束共存时执行依次：INSTEAD OF触发器触发器约束检查约束检查(Check,References)引发触发器的操作引发触发器的操作(insert,delete,update)级联操作级联操作(更新、删除更新、删除)AFTER触发器触发器注：有注：有instead of时时,跳过跳过3。1-5组成事务，组成事务

47、，任何一处有违完整性则全部回滚。任何一处有违完整性则全部回滚。An Introduction to Database System 2011 by695.6.3 定义触发器定义触发器CREATE TRIGGER 触发器名称 ON 表名|视图名 FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATE AS IF UPDATE(column)AND|OR UPDATE(column).n SQL语句 .n An Introduction to Database System 2011 by705.6.3 定义触发器定义触发器必需是批处理中的第一条语句，且仅应用一表必需是

48、批处理中的第一条语句，且仅应用一表只能在当前数据库创建，尽管可以引用外部对象只能在当前数据库创建，尽管可以引用外部对象在一个表上可以建立多个名称不同、类型各异的触发器，每在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由全部三种操作来引发。但对于个触发器可由全部三种操作来引发。但对于INSTEAD OF触触发器，在一种操作上只能建立一个触发器发器，在一种操作上只能建立一个触发器大部分大部分SQL语句都可用在触发器中，但也有限制。如语句都可用在触发器中，但也有限制。如DDL 可以运用可以运用IF UPDATE子句来测试在子句来测试在INSERT和和UPDATE语句语句中是否对指定

49、的列有影响。假如将一个值赋给指定的列或更中是否对指定的列有影响。假如将一个值赋给指定的列或更改了列的值，则这个子句就返回真改了列的值，则这个子句就返回真可以运用两个特殊的临时表：可以运用两个特殊的临时表：INSERTED和和DELETEDAn Introduction to Database System 2011 by715.6.3 定义触发器定义触发器Inserted新插入的数据新插入的数据(insert时时)更新后的数据更新后的数据(update时时)Deleted被删的数据被删的数据(delete时时)更新前的数据更新前的数据(update时时)由由DBMS自动创建和维护，与基表同构自

50、动创建和维护，与基表同构生存期：触发器内，随触发器结束而释放生存期：触发器内，随触发器结束而释放可查询，但不能修改两表的内容可查询，但不能修改两表的内容An Introduction to Database System 2011 by725.6.3 定义触发器定义触发器【例【例9】用触发器取代】用触发器取代student表中的约束表中的约束CK_ssex(例例7)。(两句须分开执行）两句须分开执行）alter table student drop CK_ssex create trigger tri_student_ssex on student for insert,update as i

展开阅读全文