幻灯片 1

Transcription

1 数据库系统原理 Database System Principles 四川大学计算机学院 段磊

2 第五章数据库完整性 完整性的概念 指数据的正确性和相容性 与安全性的比较 安全性防止人为恶意破坏数据库和非法存取 完整性防止不合语义的 不正确的数据进入数据库

3 DBMS 维护完整性的机制 定义机制 允许用户定义实体完整性 参照完整性和用户定义的完整性 检查机制 在用户的更新语句 ( 事务 ) 开始执行后检查这些操作执行后是否会违背完整性约束条件 违约处理机制 如果 DBMS 检查到用户操作违背了完整性约束条件, 就采取一定动作以保证完整性 拒绝 (No Action) 级联 (Cascade) 数据库系统概论 - 第 5 章 3/32

4 本章目录 5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名子句 5.5 域中的完整性限制 5.6 触发器 数据库系统概论 - 第 5 章 4/32

5 5.1 实体完整性 定义通过定义主码实现 例 1 定义 Student 的 Sno 为主码 create table student( sno char(10), sname char(8), sgender char(2), sage int, sdept char(2), constraint student_pkey primary key (sno) ); 数据库系统概论 - 第 5 章 5/32

6 5.1 实体完整性 定义通过定义主码实现 例 1 定义 Student 的 Sno 为主码 ( 续 ) CREATE TABLE student( sno char(10), sname char(8), sgender char(2), sage int, sdept char(2) ); ALTER TABLE student add constraint student_pkey primary key (sno); 数据库系统概论 - 第 5 章 6/32

7 实体完整性约束的检查和违约处理 检查主码值是否唯一 不唯一拒绝操作 检查主码的各个属性值是否为空 为空拒绝操作 主码上自动建立 B+ 树索引 快速检查主码值是否唯一 数据库系统概论 - 第 5 章 7/32

8 本章目录 5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名子句 5.5 域中的完整性限制 5.6 触发器 数据库系统概论 - 第 5 章 8/32

9 5.2 参照完整性约束 定义通过定义外码实现 例 3 定义 SC 表 create table sc( sno char(10), cno char(3), grade int, constraint pk_sc primary key (sno,cno), constraint sc_fkey_sno foreign key (sno) references student(sno), constraint sc_fkey_cno foreign key (cno) references course(cno), ); 数据库系统概论 - 第 5 章 9/32

10 参照完整性检查和违约处理 参照表和被参照表的增删改操作都可能破坏参照完整性 破坏参照完整性的情况 参照表插入元组, 被参照表没有对应的码值 如 SC 表中插入元组, 但 Sno 在 Student 表中无对应值 违约处理只能是拒绝 参照表修改外码值, 被参照表没有修改后对应的码值 与插入情况类似, 拒绝 数据库系统概论 - 第 5 章 10/32

11 参照完整性检查和违约处理 被参照表删除元组, 原参照表的外码值对应的码值找不到 如删除 Student 表中的某学生的元组,SC 表中改学生选课记录的 Sno 值再也找不到了 违约处理可以是拒绝 / 级联删除 / 置空值 被参照表修改主码值 如修改学生的学号 SC 表中该生对应的 Sno 值应相应处理 违约处理可以是拒绝 / 级联删除 / 置空值 有的系统不允许修改表的主码值 数据库系统概论 - 第 5 章 11/32

12 违约处理策略 拒绝操作 (NO ACTION) 不允许增删改操作 级联操作 (CASCADE) 删除被参照关系元组时, 相应删除对应的参照关系的相应元组 修改被参照关系主码值, 相应修改对应的参照关系的相应元组 置空操作 (SET NULL) 删除被参照关系元组或修改被参照关系主码值时, 将对应的参照关系元组的外码值设置为空值 数据库系统概论 - 第 5 章 12/32

13 带有处理策略的 FOREIGN KEY 子句 格式 FOREIGN KEY ( column_name [,... ] ) REFERENCES reftable [ ( refcolumn [,...] ) ] [ ON DELETE action ] [ ON UPDATE action] 数据库系统概论 - 第 5 章 13/32

14 带有处理策略的 FOREIGN KEY 子句 示例带有违约处理的 SC 表外码说明 FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACION ON UPDATE CASCADE 数据库系统概论 - 第 5 章 14/32

15 本章目录 5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名子句 5.5 域中的完整性限制 5.6 触发器 数据库系统概论 - 第 5 章 15/32

16 5.3 用户定义的完整性 列级完整性涉及单个属性的约束 列值非空 NOT NULL 列值唯一 UNIQUE 列值满足表达式 CHECK 表级完整性涉及单个或多个属性的约束 列值唯一 UNIQUE 列值满足表达式 CHECK 数据库系统概论 - 第 5 章 16/32

17 不允许取空值 例 5 在定义 Student 表时, 说明 Sname 不能取空值 CREATE TABLE student( sno char(10), sname char(8) NOT NULL, sgender char(2), sage int, sdept char(2), CONSTRAINT student_pkey PRIMARY KEY (sno) ); 数据库系统概论 - 第 5 章 17/32

18 列值唯一 例 6 创建部门表 DEPT, 要求部门名称 Dname 取值唯一, 部门编号 DeptNo 为主码 CREATE TABLE DEPT( DeptNo NUMERIC(2), Dname CHAR(9) UNIQUE, Location CHAR(10), CONSTRAINT DEPT_PKEY PRIMARY KEY (DeptNo) ); 数据库系统概论 - 第 5 章 18/32

19 列值唯一 也可写成表级约束的形式 : CREATE TABLE DEPT( DeptNo NUMERIC(2), Dname CHAR(9), Location CHAR(10), CONSTRAINT DEPT_UKEY_Dname UNIQUE(Dname), CONSTRAINT DEPT_PKEY PRIMARY KEY (DeptNo) ); 数据库系统概论 - 第 5 章 19/32

20 CHECK 约束 说明 Student 表的 Sgender 只能取 男 女 CONSTRAINT Student_check_Sgender CHECK (Sgender IN( 男, 女 )); 说明 SC 表的 Grade 在 0 到 100 分之间 CONSTRAINT sc_check_grade CHECK (Grade >=0 and Grade <=100) 数据库系统概论 - 第 5 章 20/32

21 CHECK 约束 涉及多个列值之间的比较 CREATE TABLE T1( ID char(4), oldvalue int, newvalue int, CONSTRAINT T1_PKEY PRIMARY KEY(ID), CONSTRAINT T1_CHECK CHECK(newvalue>oldvalue) ); 数据库系统概论 - 第 5 章 21/32

22 本章目录 5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名子句 5.5 域中的完整性限制 5.6 触发器 数据库系统概论 - 第 5 章 22/32

23 5.4 完整性约束命名子句 列级完整性约束一般不能显示命名 表级完整性约束可显示命名 CONSTRAINT 子句 方便用 ALTER TABLE 语句修改约束 ADD CONSTRAINT 增加约束 DROP CONSTRAINT 删除约束 数据库系统概论 - 第 5 章 23/32

24 修改 NOT NULL 约束 修改 Student 定义, 允许 Sname 为空 ALTER TABLE Student ALTER COLUMN Sname DROP NOT NULL; 又要修改为不允许空 : ALTER TABLE Student ALTER COLUMN Sname SET NOT NULL; 数据库系统概论 - 第 5 章 24/32

25 本章目录 5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名子句 5.5 域中的完整性限制 5.6 触发器 数据库系统概论 - 第 5 章 25/32

26 5.5 域中的完整性约束 create domain SQL-92 引入, 允许用户创建带有完整性约束的自定义域 create domain person_name char(20) not null; 数据库系统概论 - 第 5 章 26/32

27 本章目录 5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名子句 5.5 域中的完整性限制 5.6 触发器 数据库系统概论 - 第 5 章 27/32

28 5.6 触发器 SQL:1999 引入 用户定义在关系表上的一类由事件驱动的特殊过程 事件对表的插入 删除 修改 时机在对表的插入 删除 修改前或后 条件只有当触发条件为真时才执行 类型行级触发器语句级触发器 数据库系统概论 - 第 5 章 28/32

29 5.6 触发器 例 18 为教师表 Teacher 定义一个完整性规则触发器, 规定 教授的工资不得低于 4000 元, 如果低于 4000 元, 自动改为 4000 元 create trigger I_U_Sal after insert or update on account referencing new row as nrow //nrow 引用新值 for each row // 行级 when (nrow.job= 教授 and nrow.sal < 4000 // 条件 begin update Teacher set sal = 4000 where Tno=nrow.Tno; end 数据库系统概论 - 第 5 章 29/32

30 5.6 触发器 例 19 完成类似审计功能 补充例 :warehouse 中 4 张表 inventory(item, level): 库存量 minlevel(item, level) : 下限 reorder(item, amount): 重新订货量 orders(item, amount) : 订货量 当库存量下降到低于下界后自动按照重新订货量订货 数据库系统概论 - 第 5 章 30/32

31 5.6 触发器 create trigger reorder-trigger after update of amount on inventory referencing old row as orow, new row as nrow for each row // 新库存少于报警值 when nrow.level < = (select level from minlevel where minlevel.item = orow.item) and orow.level > (select level // 原库存大于报警值 from minlevel where minlevel.item = orow.item) begin // 触发器动作 insert into orders 在订购单中写入要新购的项目和数量 (select item, amount from reorder where reorder.item = orow.item); end 数据库系统概论 - 第 5 章 31/32

32 Any Question? Thank you! 数据库系统概论 - 第 5 章 32/32