第五章 数据库完整性

数据库完整性

注：第五章的数据库完整性是对第三章Mysql语句的补充和归纳总结，需要对Mysql语句有一定了解，以下链接可以跳转至第三章：

点这里跳转第三章哦

实体完整性

1. 实体完整性分为3类：列级定义主码、表级定义主码和只能表级定义主码

• 列级定义主码

create table students
(sno char(9) primary key, /*对学号sno这一列定义主码*/
 sname char(20) not null, /*对姓名这一列定义非空约束*/
 sex char(2),
 sage smallint,
 sdept char(20));
 /*可见以上约束均在列上定义*/

• 表级定义主码

create table students
(sno char(9),
 sname char(20) not null,
 sex char(2),
 sage smallint,
 sdept char(20),
 primary key(sno)); /*在最后面才进行表级主码的定义*/

• 只能表级定义主码

create table sc
(sno char(9),
 cno char(4),
 grade smallint,
 primary key(sno,cno)); /*组合码，表示学号和课程号一起作为主码*/

2. 实体完整性的检查规则，分为两类

• 检查主码值是否唯一：如果不唯一则拒绝插入或修改

  • 例子：假设students表的主码为sno（学号），当你想要在表中插入一个学号时会检查你所插入的学号与原来表中的学号有没有重复，有重复则插入失败，修改学号同理

• 检查主码的各个属性是否为空：只要有一个为空就拒接修改或插入

  • 例子：假设sc表的主码为组合码sno（学号）和cno（课程号），当你想要在表中插入一条新的选课记录时会检查你所插入的学号和课程号这两个属性是否有一个是空值，只要有一个属性是空值则插入失败，修改选课记录同理

3. 检查的办法，分为两类

• 全表扫描：遍历表中的所有记录来检查每一行数据是否符合实体完整性约束

  • 例子：假设students表的结构如下

  sno	sname	sex	sage	sdept
  32201280	张三	男	20	计算机系
  32201281	李四	男	20	计算机系

  在插入学号为32201282的新学生时，会从头到尾检查该表，发现没有重复的，则允许插入，可以发现全表扫描十分耗时

• B+树索引：B+ 树索引是一种高效的数据结构，插入时从根节点开始检查有没有重复

  • 当我们使用create index stusno on students(sno asc)语句时会建立索引从而形成B+树，B+ 树中的节点按键值有序排列，左子树所有值比根节点小，右子树所有值比根节点大，如下所示：

​那么当我们要插入学号32201280的学生时,假设索引记录位25,从根节点51开始 遍历,25比51小,遍历51的左子树,25比12大,比30小,则遍历12的右子树,同时也是3 的左子树,发现有重复的记录25,则拒绝插入学号为32201280的学生。可以发现使用B+树索引查找效率高，适合大规模数据集

参照完整性

1. 参照完整性的定义

create table sc
(sno char(9),
 cno char(4),
 grade smallint,
 primary key(sno,cno),
 foreign key (sno) references students(sno),
 foreign key (cno) references course(cno));

2. 参照完整性的检查规则

• 对表sc和students有四种可能破坏参照完整性的情况

  • 被参照表（例如students）	参照表（例如sc）	违约处理	例子解释
    可能破坏参照完整性	插入元组	拒绝	假设对sc表插入学号为32201282学生的选课信息，而学生表没有32201282的学生信息，则不可能有他的选课记录，拒绝执行
    可能破坏参照完整性	修改外码值	拒绝	假设要修改sc表中32201280的学生的学号为32201282，而学生表没有32201282的学生信息，则不可能有他的选课记录，拒绝执行
    删除元组	可能破坏参照完整性	拒绝/级联操作/设置为空值	假设要删除students表中的32201280的学生记录（退学或已毕业），而sc表仍存在其选课记录，则拒绝执行/级联操作/设置为空值
    修改主码值	可能破坏参照完整性	拒绝/级联操作/设置为空值	假设要修改students中的32201280的学生学号为32201282，而sc表中仍存在32201280的选课记录，则拒绝执行/级联操作/设置为空值

    可以看到当修改参照表（sc表）中的东西而影响到被参照表（students表），只有拒绝执行这一种操作；而修改被参照表中的东西而影响到参照表时有三种操作可以选择，因此我们只对后面这一种做检查办法处理

3. 检查的办法

 create table sc22
(sno CHAR(9),
 cno CHAR(9),
 grade SMALLINT,
 PRIMARY KEY(sno, cno),
 FOREIGN KEY(sno) REFERENCES students(sno) /*从这里开始设置对sno参照完整性的违约处理*/
 ON DELETE CASCADE /*级联删除sc表中相应的元组*/
 ON UPDATE CASCADE, /*级联更新sc表中相应的元组*/
 FOREIGN KEY(cno) REFERENCES course(cno) /*从这里开始设置对cno参照完整性的违约处理*/
 ON DELETE NO ACTION /*当删除course表中的元组造成了与sc表不一致时拒绝删除*/
 ON UPDATE CASCADE /*级联更新sc表中相应的元组*/ );

用户定义的完整性

1. 用户定义的完整性分为两类

• 属性上约束条件的定义

  • 不允许取空值

    • 在定义sc表时说明sno,cno,grade属性不允许空值

    create table sc
    (sno char(9) not null,
     cno char(4) not null,
     grade smallint not null,
     primary key(sno,cno));
    

  • 列值唯一

    • 在定义students表时说明sname取值唯一

    create table students
    (sno char(9) primary key,
     sname char(8) unique not null,
     sex char(2),
     sage smallint,
     sdept char(20));
    

  • 用check短语指定列值应该满足的条件

    • 在定义students表时说明sex只允许取“男”或“女”

    create table students
    (sno char(9) primary key,
     sname char(8) unique not null,
     sex char(2) check (sex in ("男","女")),
     sage smallint,
     sdept char(20));
    

    • 在定义sc表时说明grade的值应该在0和100之间

    create table sc
    (sno char(9),
     cno char(4),
     grade smallint check(grade>=0 and grade<=100),
     primary key(sno,cno),
     foreign key (sno) references students(sno),
     foreign key (cno) references course(cno));
    

• 元组上约束条件的定义

  • 当学生的性别是男时，其名字不允许以Ms.开头（涉及性别和名字这两个不同属性之间相互约束的条件，因此说是元组上约束条件的定义）

  create table students
  (sno char(9) primary key,
   sname char(8) unique not null,
   sex char(2) check (sex in ("男","女")),
   sage smallint,
   sdept char(20),
   check (sex='女' or sname not like 'Ms.%'));
   /*意思是如果性别是女则不判断，如果性别不是女则判断他的名字不应该是Ms.开头*/
  

完整性约束的其他写法

注：第三章我们其实已经把所有完整性约束怎么在建表时设置，怎么在建完表的时候添加，怎么删除，但这里有一个更一般的写法

• 添加完整性约束

  • 建立学生表students，要求学号在90000~99999之间（约束名为c1），设置为主码（约束名为students_key），姓名不能取空值（约束名为c2），年龄小于30（约束名为c3），性别只能是“男”或“女”（约束名为c3）

    create table students
    (sno numeric(6), /*注意有逗号*/
     constraint c1 check(sno between 90000 and 99999),
     sname char(8) not null, /*constraint不能用于not null*/
     sage numeric(3),
     constraint c3 check (sage<30),
     sex char(2), 
     constraint c4 check (sex in ("男","女")),
     constraint students_key primary key(sno));
    

• 删除完整性约束

  • 去掉上题对性别的约束

  alter table students
  drop constraint c4;
  

• 添加完整性约束

  • 添加上题已经去掉的对性别的约束

  alter table students
  add constraint c4 check(sex in ("男","女"));