Chap.5 数据库完整性

完整性, 这是个我们提过很多次的名词, 这一章中我们会对这个概念进行更深入的探讨, 并且了解一下SQL中如何保证完整性.

数据库完整性归根结底, 就是两方面的事情:

• 数据的正确性: 数据符合现实世界的语义
  • 比如年龄是一个数字, 而不是一个真假值
• 数据的相容性: 数据库同一对象在不同关系中的数据是符合逻辑的.
  • 比如性别只能是男或女

其实之前我们说过三类完整性, 那是一个经验公式, 即通常这三类完整性组够满足要求:

• 实体完整性(主键)
• 参照完整性(外键)
• 用户定义的完整性(自定义设置)

为了维护数据库的完整性, 数据库管理系统通常需要:

• 提供完整性约束条件的机制
• 提供完整性检查的方法
• 违约处理(就是违反完整性之后系统该怎么处理)

5.1 实体完整性

5.1.1 实体完整性的定义

在SQL中, 主键的表示我们应该挺熟悉了, 它用 PRIMARY KEY 关键字进行标识. 可以针对某一个具体属性列, 也可以针对几个属性列组成的属性集.

定义单个属性主键时只需要在对应属性列后加关键字即可:

CREATE TABLE Student {
    Sno VARCHAR(9) PRIMARY KEY,
    Sname VARCHAR(10) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept VARCHAR(20)
}

如果定义多个属性组成的属性集为主键, 则需要在表完整性行中单独定义:

CREATE TABLE SC{
    Sno VARCHAR(9),
    Cno VARCHAR(4),
    Grade INT,
    PRIMARY KEY (Sno, Cno)
}

5.1.2 实体完整性的检查与违约处理

实体完整性, 即主键的完整性是自动检查的. 会进行两方面的检查:

• 检查主码是否唯一, 不唯一则拒绝插入 / 修改
• 检查主码的各个属性是否为空, 只要一个为空就拒绝插入 / 修改

关于数据库系统如何进行这两个检查, 有两种方法:

• 全表扫描: 最容易想到的方法, 但是非常耗时, 效率低.
• 建立索引: 自动在主键上建立索引, 每次插入 / 修改时, 根据索引寻找此前是否已经存在该主键.

现在主流的数据库系统通常都采用第二种方式.

5.2 参照完整性

5.2.1 参照完整性的定义

在SQL中, 外键通过关键字 FOREIGN KEY 来进行标识, 同时通过 REFERENCES 关键字来标识它引用的是那个表的主码.

CREATE TABLE SC{
    Sno VARCHAR(9),
    Cno VARCHAR(4),
    Grade INT,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
}

5.2.2 参照完整性的检查与违约处理

参照完整性的破坏基本上就是下面四种情况导致的:

• 参照表中加了个元组, 被参照表里面找不到其外键对应的元组
• 参照表中某个元组修改后, 被参照表里面找不到其外键对应的元组
• 从被参照表中删除了一个元组, 造成参照表中某些外键找不到了
• 从被参照表中修改了一个元组, 造成参照表中某些外键找不到了

通常涉及到的处理就这三类:

• 拒绝(NO ACTION): 这通常是默认策略, 不允许该操作执行
• 级联(CASCADE): 通常是被参照表被修改 / 删除时, 会自动修改 / 删除参照表中涉及到该外键的元组
• 设置为空值(NULL): 与上述情况类似, 只不过不是级联删除 / 修改, 而是将受影响的外键更改为空值

三种策略是可以显式说明在定义中的:

CREATE TABLE SC{
    Sno VARCHAR(9),
    Cno VARCHAR(4),
    Grade INT,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
}

5.3 用户定义的完整性

用户定义的完整性指的是用户添加的, 额外需要满足的条件. 通常分两类:

• 属性上的约束
• 元组上的约束

5.3.1 属性上的约束条件

SQL提供了三种属性上的约束条件:

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

前两个其实都好办, 第三个可以说一下, 它说的条件表达式跟我们在查询中 WHERE 后面跟的那玩意是一个意思:

Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)

Ssex CHAR(2) CHECK (Ssex IN ('男', '女'))

如果不满足属性上的约束条件, 就没什么花里胡哨的了, 直接拒绝执行.

5.3.2 元组上的约束条件

元组上的约束条件指的是一个元组上多个属性相关的约束条件. 它需要在表的完整性定义行上进行书写:

CREATE TABLE Student(
    Sno CHAR(9),
    Sname CHAR(8) NOT NULL，
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY (Sno),
    CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
    /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
);

一样的, 如果违反了直接拒绝执行.

5.4 完整性约束命名子句

我们现在知道怎么添加约束了, 该怎么去除某一列上的约束呢?
这一般得分情况:

一种是针对某一个单独列的NOT NULL约束, 这种约束好清理:

ALTER TABLE Student
    ALTER COLUMN Ssex DROP NOT NULL;

它相当于直接更改列的属性.

第二种情况即像 CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE这样的约束. 它们其实在创建的时候会有一个系统自动分配的名字(被保存在数据字典里, 可以查到)

要删除它们, 需要用修改语句的 DROP CONSTRAINT 子句.

ALTER TABLE <Table_name> 
    DROP CONSTRAINT <constraint_name>;

当然, 这个名字也可以你自己来取, SQL提供了创建CONSTRAINT子句的语法:

CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999);
//一个命名为C1的完整性约束, 检查Sno在90000到99999之间

Sname CHAR(20)
CONSTRAINT C2 NOT NULL
//一个命名为C2的完整性约束, 检查Sname不空

随后如果想删除, 只需要用DROP CONSTRAINT子句来删除之前定下的约束名即可.

5.5 域中的完整性限制

在某些数据库系统中, 支持这么个叫 域 的写法, 它相当于把我们已经定义好的完整性约束全都整合起来打个包. 之后再要用这个玩意的时候, 只需要再把这个包拿出来写上去就行了.

啥意思呢:

CREATE DOMAIN GenderDomain CHAR(2)
CHECK (VALUE IN ('男', '女'));

//也可以直接对其中的某个完整性语句命名
CREATE DOMAIN GenderDomain CHAR(2)
CONSTRAINT GD CHECK (VALUE IN ('男', '女'));

这之后, 如果我们还需要划定相同的玩意, 就可以直接:

CREATE TABLE Student{
    Sno VARCHAR(9) PRIMARY KEY,
    Ssex GenderDomain NOT NULL,
    ...
}

读者可以按照理解安全性中的角色的方式一样来理解这个玩意, 它就是一个完整性约束合集.

自然, 也可以更改其内部的完整性约束:

ALTER DOMAIN GenderDomain
DROP CONSTRAINT GD
ADD CONSTRAINT GDD CHECK (VALUE IN('1', '0'));

这个玩意读者有一个印象就好, 因为不是全部的数据库都支持域这个概念. 目前主流的数据库中应该支持的只有 Postgre SQL 和 SQL Server .

5.6 断言

断言(Assertion) 可以涉及到多个表或者聚集操作的复杂约束.
违反断言时没有别的选项, 直接拒绝执行.

断言的创建格式为:

create assertion <断言名> <check子句>

我们直接给几个例子:
(1)限制数据库课程最多60名学生选修:

CREATE assertion limit_database check(
    60 >= (
        SELECT COUNT(*)
        FROM SC, Course
        WHERE Course.Sname = '数据库' AND SC.Cno = Course.Cno
    )
);

(2)限制每一门课程最多60名学生选修

CREATE assertion limit_courses check(
    60 >= ALL(
        SELECT COUNT(*)
        FROM SC
        GROUP BY Cno
    )
);

如果不希望用ALL, 也可以用聚集函数:

CREATE assertion limit_courses check(
    60 >= (
        SELECT MAX(Person)
        FROM (
            SELECT COUNT(*)
            FROM SC
            GROUP BY Cno
        ) AS Course_member(Person)
    )
);

5.7 触发器

触发器(Trigger) 是用户定义在关系表中的 由事件驱动的 特殊过程.

5.7.1 触发器的定义

触发器通常的定义结构是:

CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

有点复杂, 我们一个个说:

• 触发器名: 这个就不用说了(
• BEFORE / AFTER: 表示在触发的事件之前 / 之后执行动作体.
• 触发事件: INSERT / UPDATE / DELETE, 也可以是他们的组合.
• FOR EACH ROW / STATEMENT: 表示以元组的更改为标准还是以某一具体行语句为标准.
  • 比如, 一个SC选课表里面有1000条数据, 现在执行: UPDATE SC SET Grade = 100;
  • 如果用 FOR EACH ROW触发器( 行触发器 ), 则会触发一千次.
  • 如果用 FOR EACH STATEMENT触发器 ( 语句触发器 ) , 则会触发一次.
• REFERENCING NEW / OLD ROW AS …
  • 只在使用行触发器的时候可以这样做.
  • 相当于用两个临时变量来记录之前的行和新的行.

给个例子:
当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中: C_U(Sno,Cno,Oldgrade,Newgrade)

CREATE Trigger SC_T
AFTER UPDATE ON SC
    REFERENCING NEW ROW AS new_grade,
    REFERENCING OLD ROW AS old_grade
FOR EACH ROW
    WHEN (new_grade.Grade >= 1.1 * old_grade.Grade)
    INSERT
    INTO C_U(Sno, Cno, Oldgrade, Newgrade)
    VALUES(new_grade.Sno, new_grade.Cno, old_grade.Grade, new_grade.Grade);

注意
关于触发器的旧行和新行的引用, 在不同的触发器中是不一样的, 有些数据库系统(MySQL)甚至不支持REFERENCING这个写法. 上面写的方式是SQL标准中规定的方式.

5.7.2 触发器的删除

这个相比于定义就简单很多了:

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

这一章中, 关于完整性的说明其实比较基础, 因为在第三章的SQL中我们提到过相关的内容, 比较重要的是触发器的定义方式, 还望读者尽力理解, 看进去.