数据库完整性与SQL完整性定义

发表于 更新于 分类于

数据库完整性

数据库完整性包括

  • 数据的正确性 (数据是符合现实世界语义 , 反应了当前实际情况的)
  • 数据的相容性 (数据库同一对象在不同表中的数据是符合逻辑的)

数据库管理系统必须提供:

  1. 定义完整性约束条件的机制 (SQL的数据定义语句实现) (实体完整性, 参照完整性, 用户定义的完整性)
  2. 完整性检查机制 (INSERT UPDATE DELETE语句后开始检查, 也可以在事务提交的时候检查)
  3. 违约处理 (若发现操作违背了完整性约束条件, 采取一定操作)
    • 拒绝执行这条操作 (NO ACTION)
    • 级联执行其他操作 (CASCADE)

实体完整性

实体完整性这项规则要求每个数据表都 必须有主键 , 而作为主键的所有字段，其属性必须是 唯一及非空值 .

实体完整性定义

  • 在CREATE TABLE中用PRIMARY KEY定义主键
    • 单属性构成主键
      • 定义为 列级 完整性约束条件
      • 定义为 表级 完整性约束条件
    • 多个属性构成主键
      • 定义为 表级 完整性约束条件

例子1-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 将Student表中的Sno属性定义为主键
  # 方式1, 定义为列级约束
  CREATE TABLE Student (
      Sno CHAR(9) PRIMARY KEY,
      ......
  );
  # 方式2, 定义为表级约束
  CREATE TABLE Student (
      Sno CHAR(9),
      ...,
      PRIMARY KEY(Sno)
  );

# 将SC表中的Sno, Cno属性组定义为主键
  CREATE TABLE SC (
      Sno CHAR(9) NOT NULL,
      Cno CHAR(9) NOT NULL,
      Grade SMALLINT,
      PRIMARY KEY(Sno, Cno) # 只能在表级定义主键
  );

实体完整性检查和违约处理

插入 或对主键进行 更新 操作时, 系统按照实体完整性规则自动进行检查.

检查内容包括:

  • 检查主键值是否唯一, 若不唯一则拒绝操作
  • 检查主键的各个属性是否为空, 只要有一个为空则拒绝操作

检查的方法:

  • 全表扫描 : (一次扫描每一条记录,检查是否违约)
  • 建立索引 : (针对主键建立B+树等数据结构,提高效率)

参照完整性

参照的完整性要求关系中不允许引用不存在的实体. (外键的值必须是必须再其他表内的主码里找得到)

参照完整性的定义

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外键,
用REFERENCES短语指定这些外码参照哪些表的主键.

  • 单属性构成主键
    • 定义为 列级 完整性约束条件
    • 定义为 表级 完整性约束条件
  • 多个属性构成主键
    • 定义为 表级 完整性约束条件 (一个个指定)

外键定义时只

例子2-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# Student表中的Sdept属性是外键,参照DEPT表的主键Deptno
  # 方式1, 定义在列级参照完整性条件中
  CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sdept CHAR(20) FOREIGN KEY REFERENCES DEPT(Deptno)
    ...
  );
  # 方式2, 定义在表级参照完整性条件中
  CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sdept CHAR(20),
    ...
    FOREIGN KEY(Sdept) REFERENCES DEPT(Deptno)
  );

# 关系SC中(Sno, Cno)是主键, Sno Cno分别参照Student表的主键和Course表的主键
CREATE TABLE SC (
  Sno CHAR(9) NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY(Sno, Cno),                    # 在表级定义实体完整性
  FOREIGN KEY(Sno) REFERENCES Student(Sno), # 在表级定义参照完整性
  FOREIGN KEY(Cno) REFERENCES Course(Cno)   # 在表级定义参照完整性
);

参照完整性检查和违约处理

被参照表 和 参照表 进行 增删改 的操作时有可能破坏参照完整性, 必须进行检查.

可能 破坏参照完整性的情况已经违约处理:

被参照表 参照表 违约处理
插入元组 拒绝
修改外键值 拒绝
删除元组 拒绝 / 级联删除 / 设置为空值
修改主键值 拒绝 / 级联删除 / 设置为空值

违约处理的含义

  1. 拒绝执行 (NO ACTION)
    • 不允许该操作, 一般设置为默认策略
  2. 级联删除 (CASCADE)
    • 当删除或修改 被参照表 的一个元组造成了 参照表 不一致, 删除或修改 参照表 中造成不一致的元组
  3. 设置为空值
    • 原因同上, 将 参照表 中造成不一致的元组对应属性设置空值.

显式指定违约处理方式

例子2-2

1
2
3
4
5
6
...
FOREIGN KEY(Sno) REFERENCES Student(Sno)
  ON DELETE CASCADE,  # 当删除时出错, 级联删除
  ON UPDATE CASCADE,  # 当更新时出错, 级联更新
 #ON DELETE NO ACTION # 当删除时出错, 拒绝执行
...

用户定义的完整性

针对某一具体应用的数据必须满足的语义要求 (比如性别只能是男或女)

当用户定义的完整性被破坏时, 将拒接操作.

属性上的约束条件

CREATE TABLE时定义属性上的约束条件 :

  • 列值非空 (NOT NULL)
  • 列值唯一 (UNIQUE)
  • 检查列值是否满足一个条件表达式 (CHECK)

例子3-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#定义SC表时, 说明 Sno, Cno, Grade属性不允许取空值
CREATE TABLE SC (
  Sno CHAR(9) NOT NULL, # Sno和Cno是主键, 已经不能取空值了, 这里的NOT NULL可以省略
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT NOT NULL,
  PRIMARY KEY(Sno, Cno),
  ...
);

#建立专业表DEPT, 要求专业名Dname列取唯一值且不能为空, 专业编号为Deptno为主键
CREATE TABLE DEPT (
  Deptno NUMERIC(2),
  Dname CHAR(9) UNIQUE NOT NULL,  # 可见 这些关键字可以叠加
  PRIMARY KEY(Deptno)
);

#创建Student表的时候设置Ssex只允许取'男'或 '女'
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(8) NOT NULL,
  Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),  # CHECK短语 除了IN谓词也可以用其他谓词, 运算符等
  ...
);

属性上的约束条件检查和违约处理:

  • 插入元组或修改属性值 时, 检查属性上的约束条件是否被满足
  • 如果不满足则操作被 拒绝执行

元组上的约束条件

属性上的约束条件 : 只涉及单个属性
元组上的约束条件 : 约束条件涉及多个属性

在 CREATE TABLE 时, 用 CHECK 子句在 表级约束 上定义元组上的约束条件

例子3-2

1
2
3
4
5
6
7
8
#当学生性别是男时, 其名字不能以Ms.开头
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(8) NOT NULL,
  Ssex CHAR(2),
  ...
  CHECK (Sex = '女' OR Sname NOT LIKE 'Ms.%') #定义元组上的约束条件, 要么是女的 要么不以Ms.开头
);

元组上的约束条件检查和违约处理

  • 插入元组或修改属性的值时, 检查元组上的约束条件是否被满足
  • 如果不满足则拒绝执行

关系数据库的三类完整性约束总结

实体完整性 参照完整性 用户定义的完整性
定义方法 CREATE TABLE CREATE TABLE CREATE TABLE
检查时机 执行插入/修改操作 参照表:插入/修改 被参照表:删除/修改 执行插入/修改操作
违约处理 拒绝执行 拒绝执行/级联操作/设置为空值 拒绝执行

完整性约束命名子句

完整性约束的命名, 用于将属性及其完整性约束条件绑定. 当需要删除这个约束条件时, 删除这个命名即可.

完整性约束命名的方式 CONSTRAINT

语法

CONSTRAINT <完整性约束条件名> <完整性约束条件>
完整性约束条件 包括 : NOT NULL , UNIQUE , PRIMARY KEY , FOREIGN KEY , CHECK

例子4-1

1
2
3
4
5
6
7
8
# 建立学生表Student ,要求学号在90000-99999之间, 姓名不能为空值, 年龄小于30, 性别只能是男或女
CREATE Student (
  Sno   NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
  Sname CHAR(20)   CONSTRAINT C2 NOT NULL,
  Sage  NUMERIC(3) CONSTRAINT C3 CHECK(Sage < 30>),
  Ssex  CHAR(2)    CONSTRAINT C4 CHECK(Ssex IN ('男', '女')),
  CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

修改表中的完整性限制 ALTER TABLE

对于删除完整性限制 : 使用 ALTER TABLE ... DROP... 即可.
对于修改完整性限制 : 需要先删除, 再用 ALTER TABLE ... ADD...添加新的约束,

例子4-2

1
2
3
4
5
6
7
8
9
# 删除Student表中对性别的限制
ALTER TABLE Student DROP CONSTRAINT C4;

# 修改Student中的约束条件 : 学号改为900000-999999之间, 年龄改为小于40
ALTER TABLE Student DROP CONSTRAINT C1;
ALTER TABLE Student ADD  CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student DROP CONSTRAINT C3;
ALTER TABLE Student ADD  CONSTRAINT C3 CHECK(Sage < 40);
# 先删除旧的, 再添加新的

断言

SQL中 使用CREATE ASSERTION语句, 通过声明断言来指定更具一般性的约束.
通过断言, 可以定义一些复杂的完整性约束. 比如涉及多个表的操作 或 聚集操作.
断言创建后, 任何对断言中涉及的关系的操作都会触发对断言的检查, 任何使断言不为真的操作都拒绝执行.

定义断言 CREATE ASSERTION

语法

CREATE ASSERTION <断言名> <CHECK子句>
每个断言都有一个名字
CHECK子句的约束条件与WHERE的条件表达式类似.

例子5-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 限制数据库课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM CHECK (
  60 >= (
    SELECT COUNT(*) FROM Course, SC
    WHERE SC.Cno = Course.Cno AND Course.Cname = '数据库'
  )
);
# 当向SC插入元组时检查, 若违约则拒绝执行

# 限制每个课程最多60个学生选修
CREATE ASSERTION ASSE_SC_CNUM CHECK (
  60 >= ALL (
    SELECT COUNT(*) FROM SC GROUP BY Cno
  )
);

# 限制每个学期每门课程最多60人选修
  # 首先在SC表中增加学期TERM属性
  ALTER TABLE SC ADD TERM DATE;
  # 再用断言定义
  CREATE ASSERTION ASSE_SC_CNUM2 CHECK (
    60 >= ALL (
      SELECT COUNT(*) FROM SC GROUP BY Cno, TERM
    )
  );

如果断言很复杂, 则系统检测维护断言的开销将很高, 使用断言的时候应该注意这一点.

删除断言 DROP ASSERTION

语法

DROP ASSERTION <断言名>;

触发器 (Trigger)

触发器是用户定义再关系表上的一类由事件驱动的特殊过程
任何用户对表的增删改的操作时就自动触发
触发器可以实施更为复杂的检查和操作, 具有更精细和强大的数据控制能力

定义触发器 CREATE TRIGER

语法

语义 : 当<表名>中发生了事件<触发事件>后, 检查<触发条件>. 若满足条件则执行<触发动作体>
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> [OF <属性名>] ON <表名>
REFERENCING NEW | OLD ROW AS <变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>] <触发动作体>
触发器名 : 可包含模式名, 也可以不包含(模式名要和表名在一个模式下). 同模式下, 触发器名必须是唯一的.
BEFORE|AFTER : 触发的时机, 指定再操作执行之后或之前执行触发器
触发事件 : INSERT , DELETE , UPDATE或几个事件的组合 : UPDATE OF<触发列, …>
表名 : 触发器只能定义在基本表上, 不能定义在视图上. 表上的数据变化时,激活对应触发事件.
REFERENCING : 指出表所引用的变量
FOR EACH : 定义触发器类型(行级 / 语句级)
WHEN : 触发器激活时, 只有触发条件为真动作体才执行, 否则动作体不执行
触发动作体 : 如果是行级, 则动作体内可以用NEW OLD 否则不行. 如果动作体执行失败, 事件就终止.

触发器又叫做: 事件-条件-动作 规则

例子6-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 当修改了SC表中Grade属性时, 若分数增加了10%则此次操作记录到下表:
# SC_U (Sno, Cno, Oldgrade, Newgrade) # (学号, 课程号, 旧分数, 新分数)
CREATE TRIGGER SC_T           # 定义触发器SC_T
AFTER UPDATE OF Grade ON SC   # 当SC.Grade被更新后激活, 触发条件检查
REFERENCING                   # 引用两个变量, 分别记录修改前后的元组值
  OLD ROW AS OldTuple,
  NEW ROW AS NewTuple
FOR EACH ROW                  # 行级触发器(每执行一次更新就触发一次)
WHEN (NewTuple.Grade >= 1.1 * OldTuple.Grade)     # 新分数>=旧分数*1.1时
  INSERT INTO SC_U(Sno, Cno, Oldgrade, Newgrade)  # 若满足条件,就添加数据到SC_U表
  VALUES(OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade)

# 将每次对Student的插入操作所增加的学生数量记录到标StudentInsertLog中
CREATE TRIGGER Student_Count  # 定义触发器Student_Count
AFTER INSERT ON Student       # 当Student被插入后激活, 触发条件检查
REFERENCING                   # 引用一个变量, 记录修改后的表(里面的元组是被修改过的元组)
  NEW TABLE AS DELTA
FOR EACH STATEMENT            # 语句级触发器 (每次INSERT语句执行完执行一次)
  INSERT INTO StudentInsertLog(Numbers) # 没有WHEN短语, 不做检查每次都插入
  SELECT COUNT(*) FORM DELTA

# 定义一个BEFORE行级触发器, 为教师表Teacher定义完整性规则"教授工资不低于4000,若低于则改为4000"
CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher  # 在执行INSERT或UPDATE之前触发
FOR EACH ROW                        # 行级触发器, 没有触发条件
BEGIN
  IF (new.job = '教授') AND (new.Sal < 4000)
  THEN new.Sal := 4000;
  END IF;
END;

激活触发器

当触发器被创建后, 当满足条件时系统会自动执行, 不用显式指定

当一个表中有多个触发器, 它们的执行顺序是 :

  1. BEFORE触发器 (多个BEFORE触发器按创建时间先后/字母排序顺序执行, 不同的数据库不一样)
  2. 激活触发器的SQL语句
  3. AFTER触发器 (多个AFTER触发器的顺序与BEFORE一样)

删除触发器 DROP TRIGGER

语法

DROP TRIGGER <触发器名> ON <表名>;