第5章　数据库完整性

null数据库完整性数据库完整性数据库完整性数据库完整性 完整性—正确性、相容性、防止错误信息输入和输出。 完整性被破坏的原因：是由更新操作不遵守完整性约束条件引起的。数据的完整性和安全性的区别数据的完整性和安全性的区别数据库的完整性：是为了防止数据库中存在部分不符合语义的数据，防止错误信息的输入和输出。用以保证数据的正确、有效。 防范对象是不合语义的数据。 数据库的安全性：是为了保护数据库防止恶意的破坏和非法的存取。 防范对象是非法用户和非法操作。 二者又是密切相关的，都是使得系统随时可正常运转，防止机密信息泄露和破坏。完整性检查与完整性约束条件完整性检查与完整性约束条件为了维护数据库的完整性，DBMS必须提供一种机制，来检查数据库中的数据，看是否满足语义规定的条件。这种功能称为完整性检查。 加在数据库数据上的语义约束条件，称为完整性约束条件。他们作为模式的一部分存入数据库中。 完整性约束条件完整性约束条件 完整性约束条件是数据完整性检查的依据，是完整性控制机制的核心。 完整性约束的对象: 关系、元组、列 列约束：类型、取值范围、精度、排序等约束条件。 元组约束：元组中各个数据项之间联系的约束。 关系约束：若干元组之间、关系集合上以及关系之间的联系的约束。 如：实体完整性约束、参照完整性约束、函数依赖约束等。约束的状态：静态和动态约束的状态：静态和动态静态约束：指数据库每一确定状态时的数据对象所应满足的约束条件，它是反映数据库状态合理性的约束，最重要的一类完整性约束。 动态约束：指数据库从一种状态变为另一种状态时，新、旧值之间所应满足的约束条件，它是反映数据库状态变迁的约束。完整性约束条件分类（六种）完整性约束条件分类（六种）1.静态列级约束：列定义 2.静态元组约束：元组值应满足的条件 3.静态关系约束：实体完整性，参照完整性，函数依赖约束，统计约束。 4.动态列级约束：改变列定义或列值。 5.动态元组约束：元组新旧值之间应满足的约束条件。 6.动态关系约束：关系新旧值之间应满足的约束条件。完整性约束条件（续）完整性约束条件（续）1. 静态列级约束 静态列级约束：对的取值域的 说明 关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书 最常见、最简单、最容易实现的一类完整性约束完整性约束条件（续）完整性约束条件（续）五类静态列级约束 1) 数据类型约束：数据的类型、长度、单位、精度等 例：学生姓名的数据类型为字符型，长度为8 2) 对数据格式的约束 例： 学号：前两位 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示入学年份，后四位为顺序编号 日期：YY.MM.DD。完整性约束条件（续）完整性约束条件（续）3) 取值范围或取值集合的约束 例：规定成绩的取值范围为0-100 年龄的取值范围为14-29 性别的取值集合为[男,女] 4) 对空值的约束 空值：未定义或未知的值，与零值和空格不同 有的列允许空值，有的则不允许 5) 其他约束 例：关于列的排序说明，组合列等完整性约束条件（续）完整性约束条件（续）2. 静态元组约束 规定元组的各个列之间的约束关系 例：订货关系中发货量<=订货量 静态元组约束只局限在元组上 完整性约束条件（续）完整性约束条件（续）3. 静态关系约束 关系的各个元组之间或若干关系之间存在的各种联系或约束 常见静态关系约束： 1) 实体完整性约束 2) 参照完整性约束 3) 函数依赖约束 4) 统计约束统计约束统计约束定义某个字段值一个关系多个元组的统计值之间的约束关系 例：职工平均工资的2倍<=部门经理的工资<= 职工平均工资的5倍 职工平均工资值: 统计值完整性约束条件（续）完整性约束条件（续）4. 动态列级约束 动态列级约束是修改列定义或列值时应满足的约束条件 完整性约束条件（续）完整性约束条件（续） 1) 修改列定义时的约束 例：将原来允许空值的列改为不允许空值时: 该列目前已存在空值，则拒绝这种修改 2) 修改列值时的约束 修改列值时新旧值之间要满足的约束条件 例：职工工资调整 >= 原来工资 年龄只能增长完整性约束条件（续）完整性约束条件（续）5. 动态元组约束 修改元组值: 各个字段之间要满足的约束条件 例: 职工工资调整不得低于其原来工资 + 工龄*1.5 6. 动态关系约束 关系变化前后状态：限制条件 例：事务一致性、原子性等约束条件 完整性约束条件小结完整性约束条件小结完整性控制(I)完整性控制(I)DBMS的完整性控制机制应具有以下三个方面的功能： 定义功能：提供定义完整性约束条件的机制。 检查功能：检查用户发出的操作请求是否违背了完整性约束条件。 如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。 完整性规则：五元组（D，O，A，C，P） 其中，D(Data): 约束作用的数据对象； O(Operation): 触发完整性检查的数据库操作, 即当用户发出什么操作请求时需要检查该完整性规则，是立即检查还是延迟检查；完整性控制(II)完整性控制(II) A(Assertion): 数据对象必须满足的断言或语义约束，这是规则的主体； C(Condition): 选择A作用的数据对象值的谓词； P(Procedure): 违反完整性规则时触发的过程。 例1，在“学号不能为空”的约束中， D：约束作用的对象为Sno属性； O：插入或修改Student元组时； A：Sno不能为空； C：无（A可作用与所有 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 的Sno属性）； P：拒绝执行该操作。完整性控制(III)完整性控制(III)例2，在“教授工资不得低于1000元”的约束中， D：约束作用的对象为工资Sal属性； O：插入或修改职工元组时； A：Sal不能小于1000； C：职称=’教授’（A仅作用于职称=’教授’记录）； P：拒绝执行该操作。 在关系系统中，最重要的完整性约束是实体完整性和参照完整性，其他完整性约束条件可归入用户定义的完整性。SQL SERVER中的完整性控制方法SQL SERVER中的完整性控制方法实体完整性 primary key constraints 参照完整性 foreign key constraints 用户自定义完整性 Unique constraints Check constraints Default constraints Nullability constraints Trigger 5.1 实体完整性5.1 实体完整性PRIMARY　KEY 在列级定义主码 在表级定义主码 在列级定义主码-Student在列级定义主码-StudentCREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) unique, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) )在列级定义主码- Course在列级定义主码- CourseCREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT ) 在表级定义主码-Student在表级定义主码-StudentCREATE TABLE Student (Sno CHAR(9), Sname CHAR(20) unique, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY(sno) )多个属性的组合作为主码， 只能在表级定义主码-SC多个属性的组合作为主码， 只能在表级定义主码-SCCREATE TABLE SC (Sno CHAR(9) , cno CHAR(4) , Grade SMALLINT, PRIMARY KEY(Sno,Cno) )实体完整性检索和违约处理实体完整性检索和违约处理定义了主码之后，当insert or update主码时，DBMS自动进行检查： 主码是否唯一， 主属性是否为空 检查方法：全表扫描、B+树索引5.2 参照完整性5.2 参照完整性Foreign key 在列级定义外码 在表级定义外码 在列级定义外码-sc在列级定义外码-sccreate table sc (sno char(9) not null references student(sno) , cno char(4) not null references course(cno), grade smallint, primary key (sno,cno) ) 在表级定义外码-sc在表级定义外码-scCREATE TABLE SC (Sno CHAR(9) , cno CHAR(4) , Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES student(sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ) 1、外码能否接受空值问题：依赖于应用环境的语义 1、外码能否接受空值问题：依赖于应用环境的语义 2、被参照的关系中删除元组的问题。2、被参照的关系中删除元组的问题。级联删除（CASCADES）。将参照关系中所有外码值与被参照关系中要删除的元组主码值相同的元组一起删除。如果参照关系同时又是另一个关系的被参照关系，则这种删除操作会继续级联下去。 受限删除（RESTRICTED）。仅当参照关系中没有任何元组的外码值与被参照关系中要删除元组的主码值相同时，系统才执行删除操作，否则拒绝此删除操作。 置空值删除（NULLIFIES）。删除被参照关系的元组，并将参照关系中相应元组的外码值置空值。 null例：要删除Student关系中Sno=950001的元组， 而SC关系中有4个元组的Sno都等于950001。 级联删除：将SC关系中所有4个Sno=950001的元组一起删除。如果参照关系同时又是另一个关系的被参照关系，则这种删除操作会继续级联下去 受限删除：系统将拒绝执行此删除操作。 置空值删除：将SC关系中所有Sno=950001的元组的Sno值置为空值。 在学生选课数据库中，显然第一种方法和第二种方法都是对的。第三种方法不符合应用环境语义。 3、在参照关系中插入元组时的问题。3、在参照关系中插入元组时的问题。受限插入。仅当被参照关系存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。 递归插入。该策略首先在被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。 null例：向SC关系插入（99001，1，90）元组，而Student关系中尚没有Sno=99001的学生 受限插入：系统将拒绝向SC关系插入（99001，1，90）元组 递归插入：系统将首先向Student关系插入Sno=99001的元组，然后向SC关系插入（99001，1，90）元组。 4. 修改被参照关系中主码的问题4. 修改被参照关系中主码的问题两种策略 (1)不允许修改主码 (2)允许修改主码 允许修改主码策略允许修改主码策略违约操作 要修改被参照关系中某些元组的主码值，而参照关系中有些元组的外码值正好等于被参照关系要修改的主码值 要修改参照关系中某些元组的主码值，而被参照关系中没有任何元组的外码值等于参照关系修改后的主码值 允许修改主码策略允许修改主码策略违约反应 (1) 修改的关系是被参照关系：与删除类似 级联修改 受限修改 置空值修改允许修改主码策略允许修改主码策略违约反应 (2) 修改的关系是参照关系：与插入类似 受限修改 递归修改允许修改主码策略允许修改主码策略 例：将Student关系中Sno=950001的元组中Sno值改为960123。而SC关系中有 4个元组的Sno=950001 级联修改：将SC关系中4个Sno=950001元组中的Sno值也改为960123。如果参照关系同时又是另一个关系的被参照关系，则这种修改操作会继续级联下去。 受限修改：只有SC中没有任何元组的Sno=950001时，才能修改Student表中Sno=950001的元组的Sno值改为960123。 置空值修改：将Student表中Sno=950001的元组的Sno值改为960123。而将S表中所有Sno=950001的元组的Sno值置为空值。 在学生选课数据库中只有第一种方法是正确的。nullCreate table student (sno char(2) primary key, Sname char(8) ) Create table sc (sno char(2) references student(sno) On delete cascade On update cascade, Cno char(2), Grade int) 用户定义的完整性用户定义的完整性Unique constraints Check constraints Default constraints Nullability constraints Trigger UNIQUE 约束UNIQUE 约束可使用 UNIQUE 约束确保在非主键列中不输入重复值。 尽管 UNIQUE 约束和 PRIMARY KEY约束都强制唯一性，但在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束： 非主键的一列或列组合。 一个表可以定义多个 UNIQUE 约束，而只能定义一个 PRIMARY KEY 约束。 允许空值的列。 允许空值的列上可以定义 UNIQUE 约束，而不能定义 PRIMARY KEY 约束。 FOREIGN KEY 约束也可引用 UNIQUE 约束。CHECKCHECKCHECK 约束通过限制输入到列中的值来强制域的完整性。 CHECK 约束从逻辑表达式判断而非基于其它列的数据。 例如，通过创建 CHECK 约束可将 salary 列的取值范围限制在 $15,000 至 $100,000 之间，从而防止输入的薪金值超出正常的薪金范围。 可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的逻辑（布尔）表达式来创建 CHECK 约束。对上例，逻辑表达式为：salary >= 15000 AND salary <= 100000 对单独一列可使用多个 CHECK 约束。按约束创建的顺序对其取值。 nullCREATE TABLE SCAA (Sno CHAR(9) , cno CHAR(4) CHECK (CNO IN('1','2','3')), Grade SMALLINT CHECK(grade>=0 and Grade<=100), PRIMARY KEY(Sno,Cno) )完整性约束命名子句完整性约束命名子句TRIGGERTRIGGER一类由数据库操作事件驱动的特殊过程 一旦由某个用户定义，任何用户对该触发器指定的数据进行增、删、改操作时，系统将自动激活相应的触发动作，在数据库服务器上进行集中的完整性控制。触发器使用时机触发器使用时机1） 对库中相关表进行连环更新，如：键值的同步更新，数据冗余实现，计算表的同步更新等； 2） 实现那些破坏完整性操作的拒绝，如：不匹配外键值的插入拒绝； 3） 实现库定义本身所不能实现的更为复杂的商业规则，如：更新操作的时间限制，更新数据的幅度限制等； 4） 实现简单的“如果……怎么办”的分析。 触发器触发器触发器是一条语句，当对数据库做修改时，它自动被系统执行 触发器的定义 指明什么条件下触发器被执行 指明触发器执行的动作是什么 触发器的作用 示警 满足特定条件时自动执行某项任务 触发器事件 Insert、 delete、updatenullINSERT 触发器工作原理 DELETE 触发器工作原理 UPDATE触发器工作原理 触发器工作原理Inserted 和 Deleted介绍Inserted 和 Deleted介绍触发器语句中使用了两个特殊的表：Inserted和 Deleted，它们由系统自动创建和管理,存储在内存中而非数据库中。 inserted 和 deleted表结构与被触发作用的表结构相同 可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；但不能直接对表中的数据进行更改。 执行完毕后 与触发器相关的两个表都会被删除。nullInserted 用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。 Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted表和触发器表通常没有相同的行。 触发器工作原理触发器工作原理Insert触发器：当执行insert语句时，先向inserted表中插入一个新行的副本，然后检查inserted表中的新行是否有效，确定是否要阻止该insert操作。如果所插入的行中的值是有效的，则将该行插入到触发器表。 Delete触发器：当执行delete语句时，先将要删除的所有行移到deleted表中，然后检查deleted表中的数据是否需要阻止，如果不进行，则将触发器中的数据行删除。 nullUpdate触发器：当执行update语句时，相当于执行一个delete操作，再执行一个insert操作，旧的行先被移动到deleted表。 三、使用T-SQL管理触发器三、使用T-SQL管理触发器1、创建触发器 Create trigger trigger_name On table/view {for /after/instead of} {insert/update/delete} As Sql_statement创建触发器示例创建触发器示例 create trigger welcom_stu on student after insert as print '欢迎你，新同学！' go create trigger welcom_stu 11 on student Instead of insert as print ‘欢迎你，亲！' go nullcreate trigger byby_stu on student after delete as print '再见，我的同学！' go 下面你插入一条数据看看： 下面你插入一条数据看看： insert into student(sno,sname,ssex,sage,sdept) values('99120','张星星','男',19,'mis') 然后，你再删除一条数据看看： delete from student where sname='张星星' 例子表结构例子表结构create table employee( eno char(4) primary key, ename char(9), title char(8)) create table salary( 　　eno char(4) , 　　basepay int, service int, rest int)例1例1设数据库中有一职工历史表emp_his,为employee创建一个触发器，当删除职工时，将删除职工的信息保存到表emp_his create table emp_his( eno char(4) primary key, ename char(9), title char(8)) nullcreate trigger del_emp1 　　on employee 　　 for delete as 　　insert into emp_his 　　　 select * from deleted例2例2为employee建立触发器，禁止删除职工号为0001的职工。nullCreate trigger del_eno On employee For delete As If (select eno from deleted)=‘0001’ begin print ‘he is vip!deleted failed’ rollback transaction end例3例3为employee创建一个触发器，当插入一职工时，在工资表中生成该职工的工资记录，设置其基本工资为500，其他工资为0nullcreate trigger ins_salary on employee for insert as declare @eno char(4) select @eno=eno from inserted insert into salary(eno ,basepay , service, rset ) values(@eno,500,0,0)例4例4为employee创建一个触发器,当修改某位职工的职称时，应同时修改salary表中的基本工资，具体标准是：当职称修改为工程师时，增加150元；当修改为高工时，增加300元。nullcreate trigger title_salary on employee for update as if update(title) if (select title from inserted)='工程师' update salary set basepay=basepay+150 where eno=(select eno from inserted) else if (select title from inserted)='高工' update salary set basepay=basepay+300 where eno=(select eno from inserted)nullcreate trigger title_salary on employee for update as if update(title) declare @title char(8) select @title= title from inserted declare @eno char(8) select @eno= eno from inserted if @title='工程师' update salary set basepay=basepay+150 where eno=@eno else if @title='高工' update salary set basepay=basepay+300 where eno=@eno小结小结完整性机制的实施会极大地影响系统性能 不同的数据库产品对完整性的支持策略和支持程度是不同的 许多数据库管理系统对完整性机制的支持比对安全性的支持要晚得多也弱得多 数据库厂商对完整性的支持越来越好，不仅在能保证实体完整性和参照完整性而且能在DBMS核心定义、检查和保证用户定义的完整性约束条件