七、数据库的完整性

发布于：2025-06-08 ⋅ 阅读:(19) ⋅ 点赞:(0)

七、数据库的完整性

主要内容

7.1 数据库的完整性概述

7.2 实体完整性

7.3 参照完整性

7.4 用户定义的完整性

7.5 触发器

7.6 SQL Server中数据库完整性的实现

7.7 小结

7.1 数据库的完整性概述

  • 数据库完整性的含义

    • 正确性
      指数据的合法性

    • 有效性
      指数据是否属于所定义的有效范围

    • 相容性
      表示同一事实的两个数据应相同

  • 保持完整性的目的

  • 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据 （防止不合语义的、不正确的数据）

  • 保证数据库的数据质量

  • 为维护数据的完整性，需要对数据库中的数据施加一些语义约束条件（完整性约束条件或完整性规则），对数据库进行完整性控制。

  • 数据库的完整性控制是 DBMS 的基本功能之一，由 完整性子系统 负责，其功能包括：

    • 提供定义完整性约束条件的机制
    • 提供检查完整性的方法
    • 提供违约处理
      • 拒绝执行
      • 按照完整性控制策略处理

  • 完整性约束条件

    • 一种语义概念；
    • 是对数据库中数据本身的 某种语义限制、数据间的逻辑约束、数据变化时所遵循的规则 等。
    • 关系数据库对数据的各种限制是以完整性约束条件的形式 在关系数据库模式中指定的。
    • SQL 标准 使用了一系列概念来描述完整性，包括关系模型的实体完整性、参照完整性和用户定义完整性。

  • 完整性检查

    • DBMS 检查数据库中的数据是否满足完整性约束条件

  • 关系数据库的完整性约束条件分类：

    • 实体完整性约束
    • 参照完整性约束
    • 其他（用户定义的）完整性约束

  • 完整性约束条件的作用对象：

    • 关系（关系约束）
    • 元组（元组约束）
    • 属性列（列级约束）

  • 列级约束

    主要是对属性的数据类型、数据格式和取值范围、精度等的约束。具体包括：

    • 对 数据类型 的约束，包括数据类型、长度、精度等的约束。例如：学生姓名的数据类型是字符串，长度是 8。

    • 对 数据格式 的约束例如规定日期的格式为 YYYY/MM/DD

    • 对取 值域 的约束，例如：学生成绩的取值范围必须是 0~100

    • 对 空值 的约束

  • 元组约束

    元组中各个属性之间的约束关系

    例如：订货关系中发货日期不能小于订货日期，发货量不得超过订货量

  • 关系约束

    一个关系的各个元组之间，或者多个关系之间存在的各种联系或约束。常见的关系约束有：

    • 实体完整性约束
    • 参照完整性约束
    • 函数依赖约束
    • 统计约束等

  • 在关系数据库系统中，完整性控制策略包括：默认值，约束，规则，触发器和存储过程

    • 默认值（Default）

      • 如果在插入行中没有指定列的值，那么默认值指定列中所使用的值。
        例如：自动增长值、内置函数、数学表达式等。

    • 约束（Check）（检查、校验）

      • 是自动强制数据完整性的方法。
      • 定义关系列中允许值的规则，是通用的强制完整性的标准机制。
      • 使用 Check 可用于触发器、规则和默认值。

    • 规则（Rule）

      • 规则是大多数数据库系统中一个向后兼容的功能，用于执行一些与 Check 约束相同的功能。

      • 规则以单独的对象创建，然后绑定到列上。

      • 早期 SQL Server 中特有的功能，后来基本被 Check 所取代。

        可以把一些检查条件单独定义成一个“规则对象”，再绑定到字段上。

        举例：

        CREATE RULE PosInt AS @value > 0;
EXEC sp_bindrule PosInt, 'Student.Age';

    • 触发器（Trigger）

      • 一种数据库事件响应机制。
      • 当表的内容发生变化（如插入/删除/修改）时自动执行的存储程序。

      • 触发器是数据库系统中强制业务规则和数据完整性的主要机制。

      • 用编程的方法实现复杂的业务规则和约束。

      • 常用于强制“复杂业务逻辑”或“级联更新/验证”。

      • 例如：

        CREATE TRIGGER CheckSalary
ON Employee
FOR INSERT, UPDATE
AS
BEGIN
  IF EXISTS (SELECT * FROM inserted WHERE salary < 0)
    ROLLBACK TRANSACTION;
END;

    • 存储过程（Procedure）

      • 与触发器类似。
      • 是一组预先写好、存储在数据库中的SQL语句集合。
      • 可以像函数一样调用。
      • 虽然不直接用于完整性约束，但能实现更复杂的控制逻辑。

    • 断言（Assertion）

      • 是 SQL 标准中定义的一种“表级约束”机制。作用于整个数据库，而不仅限于某个表字段。
      • 例如：
      create assertion <name> check(predicate)
      • 注意：大多数商用数据库（如 MySQL、SQL Server）并不支持断言。

  • 数据完整性的另一种分类

    • 声明式数据完整性（非编程的方式，解释的方式）

      • 作为对象定义的一部分来定义数据必须达到的标准
      • DBMS 自动强制完整性
      • 通过使用 约束、默认值 和 规则 来实现

      说明：声明式数据完整性也称为静态约束、结构性控制

      • 用语法声明约束：你只需用数据库语言（如SQL）声明某列/某表的约束，DBMS 会自动检查。

      • 定义在数据库对象中（如表结构内），是数据定义的一部分。

      • 自动由 DBMS 强制，程序员无需再手动检查。

      • 实现方式：

        • 使用 CHECK 约束
        • 使用 DEFAULT 默认值
        • 使用 RULE 规则（已被废弃，但仍有文献提及）

      举例：

      CREATE TABLE Student (
  Age INT CHECK (Age >= 0 AND Age <= 150),
  Gender CHAR(1) DEFAULT 'M'
);

    • 过程式数据完整性（编程方式）

      • 在脚本中定义数据必须达到的标准
      • 在脚本中强制完整性
      • 通过使用 触发器 和 存储过程 来实现
      • 可在客户端或服务器用其他编程语言和工具来实现

      进一步解释，过程式数据完整性（编程方式）也称为动态约束、程序性控制，特点是：

      • 在脚本中通过代码逻辑来控制数据是否符合完整性要求。
      • 通常适用于复杂逻辑、跨表或跨库的约束。
      • 程序员手动实现完整性控制，可以写在：
        • 存储过程（Procedure）
        • 触发器（Trigger）
        • 外部应用代码（Python、Java 等）

      举例（触发器实现）

      CREATE TRIGGER trg_CheckSalary
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
  IF NEW.Salary < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END;

7.2 实体完整性

  • 实体完整性规则规定：
    • 主键的值唯一
    • 主键的值不能取空值
  • 实现方法：通过对主键值的约束实现实体完整性
  • 关系模型的实体完整性定义：
    • 用 PRIMARY KEY 定义：
      • 定义为列级约束条件
      • 定义为表级约束条件
    • 单属性构成的主键有两种定义方式
    • 对多个属性构成的主键只能定义为表级约束条件

【例】实体完整性约束定义

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

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

CREATE TABLE SC
(
  Sno     CHAR(9)  NOT NULL,
  Cno     CHAR(4)  NOT NULL,
  Grade   SMALLINT,
  PRIMARY KEY (Sno, Cno)  -- 只能在表级定义主键
);

  • PRIMARY KEY 约束可以作为表定义的一部分在创建表时定义，也可以在表创建之后再添加。

  • 为了实施实体完整性，系统一般会在主键属性上自动创建唯一的索引来强制唯一性约束。

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

    定义表的主键后，每当对该表插入一条记录或者对主键进行更新操作时，DBMS 自动进行是实体完整性的检查

    • 检查主键是否唯一，
    • 如果不唯一，则拒绝进行插入或修改
    • 检查主键的各个属性（字段）值是否为空
      • 如果有空的字段值，则拒绝操作，从而保证实体完整性。
    • 检查方法：全表扫描法，索引扫描法。

  • 使用全表扫描法检查记录中主键值是否唯一

    image-20250606095621372

  • 使用索引判断主键是否存在

    image-20250606095628862

7.3 参照完整性

  • 参照完整性
    • 刻画不同关系之间的联系
    • 约束同一关系内部不同属性之间的联系
  • 参照完整性约束规则
    • 不允许引用不存在的元组
    • 在关系模型中，外键字段的值要么为空值，要么是被引用关系中元组的对应值
  • 关系模型的参照完整性定义
    • 用 FOREIGN KEY 短语定义哪些列为外键
    • 用 REFERENCES 短语指明外键参照哪些表的主键

【例】定义 SC 中的参照完整性

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)
);

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

  • 外键将两个表之间的元组联系起来，建立了参照与被参照的联系，需要维护参照完整性

    image-20250606101527515

  • 破话参照完整性的情况及其处理

    image-20250606101555861

  • 维护参照完整性的策略

    1. 参照关系中外键空值的问题
      • 需要定义外键是否允许为空值
      • 如果，外键是其主键的组成部分，外键值不允许为空
      • 如果，外键不是其主键的组成部分，可以根据具体的语义确定外键值是否允许为空
      • 允许空值的存在使得 SQL 中参照完整性约束的语义变得更加复杂
    2. 在参照关系中插入元组的情况

      • 受限插入（RESTRICTED）：

        • 向 titles 中插入新元组，但该元组的 pub_id 属性值在表 publishers 中不存在，则系统拒绝插入

      • 递归插入【级联 (CASCADE) 插入】：

        • 首先向被参照关系插入相应的元组，其主键值等于参照关系插入元组的外键值，然后再向参照关系插入该元组
    3. 在参照关系中修改元组的方式
      • 先删除、再插入
    4. 在 被参照关系 中删除元组的情况

      • 级联删除（CASCADE）

        • 将参照关系中所有外键值与被参照关系中要删除元组之间值相同的元组一起删除。
        • 如果参照关系同时又是另一个关系的被参照关系，这种删除操作会继续级联下去。

      • 受限制删除（RESTRICTED）

        • 仅当参照关系中没有任何元组的外键值与被参照关系中要删除元组的主键值相同时，系统才执行删除操作，否则拒绝删除操作。

      • 置空值删除

        • 删除被参照关系的元组，并将参照关系中相应元组的外键值置为空值。
    5. 在 被参照关系 中修改主键值的方式
      • 先删除、再插入

  • 违反参照完整性，系统一般选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建表时加以说明。

显式说明参照完整性的违约处理示例

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)
        ON DELETE CASCADE     -- 级联删除 SC 表中相应的元组
        ON UPDATE CASCADE,    -- 级联更新 SC 表中相应的元组

    FOREIGN KEY (Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION   -- 删除 Course 表中元组造成与 SC 不一致时拒绝删除
        ON UPDATE CASCADE     -- 更新 Course 表中的 Cno 时，级联更新 SC 表中相应的元组
);

7.4 用户定义的完整性

  • 用户定义的完整性是限定某一具体应用的数据必须满足的语义要求：
    • 列值不能为空
    • 列值唯一
    • 一定的数据格式
    • …
  • RDBMS 提供定义和检验用户定义的完整性的机制，不必由应用程序承担这部分功能。
  • 用户定义的完整性的定义

    • 属性上的约束条件的定义：

      • 唯一（UNIQUE）
      • 非空（NOT NULL）
      • CHECK 约束
      • 默认值 DEFAULT 等

    • 元组上的约束条件的定义：

      • 用 CHECK 短语定义元组上的约束条件
      • 元组级的限制可以设置不同属性之间的相互约束条件

【例】创建选课表并添加属性约束条件

CREATE TABLE SC(
	Sno CHAR(9) NOT NULL,
	Cno CHAR(4) NOT NULL,
	GRADE SMALLINT CHECK(Grade >= 0 AND Grade <= 100),
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
  • SMALLINT：
    • 类型：整数类型
    • 范围
      • 有符号（默认）：-32768到32767
      • 无符号（UNSIGNED）：0 到 65,535
    • 用途：适合用于存储不太大的整数值，比如年龄、成绩、评分等。

【例】创建 Students 表，其中 age 值必须在 10 到 25 之间，score 值必须在 0 到 100 之间

CREATE TABLE Student(
	Sno CHAR(9)  NOT NULL,
    Name VARCHAR(50),
    age INT ,
    score DECIMAL(5,2),
    CHECK(age BETWEEN 10 AND 25 AND score BETWEEN 0 AND 100)
);

  • VARCHAR(n)：

    • 类型：可变长度字符串。
    • n 表示最大长度，单位是字符（不是字节）。
    • 用途：适合用于存储长度不固定的文本，比如姓名、地址、邮箱等。
    • 示例：VARCHAR(50) 表示最多可以存储 50 个字符。

  • DECIMAL(m, d)：

    • 类型：定点数（用于存储精确小数，不会有浮点数误差）。

    • m：总位数（包括小数点前后）

    • d：小数点后的位数

    • 示例：DECIMAL(5, 2)

      • 最大值为 999.99，因为最多 5 位数，其中 2 位在小数点后。
      • 用途：适合存储对精度要求高的数值，如金额、评分等。

【例】创建 table1，指定 c1 字段不能包含重复值，c2 字段只能取特定值

CREATE TABLE table1(
	c1 CHAR(2) UNIQUE,
	c2 CHAR(4) CHECK (c2 IN ('0000','0001','0002','0003')),
    c3 INT DEFAULT 1
);

  • 用户定义完整性的检查与违约处理

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

    • 例：

      (1) INSERT INTO table1 (c1, c2)
    VALUES ('10', '0000');

(2) INSERT INTO table1
    VALUES ('10', '0001', 2);

    • 报错：

      服务器：消息 2627，级别 14，状态 2，行 1
违反了 UNIQUE KEY 约束 'UQ_table1_4222D4EF'。不能在 table1 中插入重复键。
语句已终止。

  • 定义 CONSTRAINT 约束的完整格式

    CONSTRAINT <完整性约束条件名>
    [ PRIMARY KEY 短语 |
      FOREIGN KEY 短语 |
      CHECK 短语 ]

    示例：

    CREATE TABLE 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 语句修改表中的完整性限制。

    • 注意：需要先删除原来的约束条件，再增加新的约束条件。

    • 示例： 修改表 Student 中的约束条件，将年龄由小于 -+

    • 30 改为小于 40

      ALTER TABLE Student
    DROP CONSTRAINT C3;

ALTER TABLE Student
    ADD CONSTRAINT C3
    CHECK (Sage < 40);

7.5触发器

  • 数据库系统一般提供两种主要机制来实现业务规则和数据完整性：

    • 约束（constraint）（声明式，静态的）

      • 完整性约束机制在检测出违反约束条件的操作后，只能作简单的动作，例如：拒绝操作。

    • 触发器（trigger）（过程式，动态的）

      • 触发器是用户定义在关系数据表上的一种由事件驱动的特殊过程，用编程的方法实现复杂的业务规则。
      • 触发器比约束更加灵活，可以实现一般的数据完整性约束实现不了的复杂的完整性约束，具有更精细和更强大的数据控制能力。
      • 触发器常常用于强制业务规则和数据完整性。

  • 触发器是一种特殊类型的存储过程，在对表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行。

  • 可用触发器完成很多数据库完整性保护的功能：

    • 实现复杂的业务规则
    • 实现比声明式约束更复杂的数据完整性
    • 比较数据修改前后的状态
    • 维护非规范化数据

定义触发器

  • SQL 使用 CREATE TRIGGER 命令创建触发器，其一般格式为：

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

    发器也叫事件－条件－动作（event-condition-action）规则

    各组成部分解释：

    • CREATE TRIGGER <触发器名>：定义一个触发器并命名。
    • { BEFORE | AFTER }：触发时间，表示是在事件执行前还是后触发。
    • <触发事件>：如 INSERT、UPDATE、DELETE，即触发条件。
    • ON <表名>：指定要监视的表。
    • REFERENCING ... AS ...：为旧值和新值指定别名，通常在行级触发器中使用。
    • FOR EACH ROW：表示这是行级触发器，每一行变更都会触发；STATEMENT 表示语句级触发器。
    • [WHEN <条件>]：可选，指定进一步的触发条件。
    • <触发动作体>：触发后自动执行的 SQL 语句块。

    示例：

    CREATE TRIGGER SC_T
	AFTER UPDATE OF Grade ON SC
	REFERENCES
		OLD row AS OldTuple,
		NEW row AS NewTuple
	FOR EACH ROW
	WHEN (NewTuple.Grade >= 1.1* OldTuple.Grade)
	INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
	VALUES (OldTuple.Sno,OldTuple.Cno,
            OldeTuple.Grade,NewTuple.Grade);

    含义解析：

    • 触发器名：SC_T
    • 触发时机：当对 SC 表的 Grade 字段进行 UPDATE 操作之后（AFTER UPDATE OF Grade）
    • 作用范围：行级触发器（FOR EACH ROW）
    • 条件：仅当新成绩大于原成绩的 1.1 倍时（即涨幅超过 10%），才触发（WHEN 子句）
    • 操作内容：将旧值和新值插入到 SC_U 表中用于日志记录或审计

  • MySQL版本

    • MySQL 中的 CREATE TRIGGER 基本语法：

      CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    FOR EACH ROW
BEGIN
    -- 触发器主体 SQL 语句块
END;

    • 示例，MySQL 等价写法如下（MySQL 不支持 WHEN）

      
CREATE TRIGGER SC_T
AFTER UPDATE ON SC
FOR EACH ROW
BEGIN
    IF NEW.Grade >= 1.1 * OLD.Grade THEN
        INSERT INTO SC_U(Sno, Cno, OldGrade, NewGrade)
        VALUES (OLD.Sno, OLD.Cno, OLD.Grade, NEW.Grade);
    END IF;
END;

      • MySQL 中需要注意的差异点：

        项目 PostgreSQL/Oracle 风格 MySQL
        WHEN 子句 支持 不支持，需要用 IF 包裹
        REFERENCING 支持 NEW/OLD 重命名 不支持，固定用 NEW 和 OLD
        多事件触发 可以指定 UPDATE OF Grade 不支持 UPDATE OF，只能 AFTER UPDATE
        多语句体 PL/SQL 或 PL/pgSQL 块 用 BEGIN...END 包裹，需配合 DELIMITER
        • MySQL 中创建触发器非常常见，但功能稍弱，需用 IF 判断代替 WHEN。
        • 不支持在同一个表上为同一事件类型（如 AFTER UPDATE）创建多个触发器（每个表每种事件最多一个触发器）。

定义触发器的语法说明

  1. 触发器的创建者

    • 表拥有者或创建表的用户才可以在表上创建触发器
    • 一个表上可以创建多个触发器

  2. 表名

    • 触发器的目标表
    • 当目标表被更新时，触发器才被激活

  3. 触发事件

    • 定义激活触发器的 SQL 语句的类别
    • INSERT、DELETE、UPDATE，或三者组合

  4. 触发时间

    • 指明触发器何时执行
    • BEFORE
      • 在触发事件执行之前，判断触发条件是否满足。
      • 若满足条件则先执行触发动作部分的操作，然后再执行触发事件的操作。
    • AFTER
      • 在触发事件完成之后，判断触发条件是否满足。
      • 若满足条件则执行触发动作部分的操作。
      • 如果触发事件因错误（如违反约束或语法错误）而失败，触发器将不会执行<触发动作>，<触发事件>依然执行。

  5. 触发器类型
    按照触发动作的间隔尺度，触发器可分为：

    • 行级触发器（FOR EACH ROW）：
      对每一个修改的元组都会触发触发器的检查和执行。
    • 语句级触发器（FOR EACH STATEMENT）：
      只在 SQL 语句执行时执行触发器的检查和触发器的执行。

    例：Teacher 表上创建一个 AFTER UPDATE 触发器。如果表 Teacher 有1000行，执行如下语句：

    UPDATE Teacher SET Deptno=5;

    • 语句级触发器，触发动作将执行1次。

      通常用于：

      • 日志记录操作（不关心每一行的修改）
      • 批量处理：如果需要进行一些与整个SQL语句相关的操作，而不需要对每一行进行细致的处理，语句级触发器会更高效。比如，执行批量更新时，可能只关心整个操作的时间或其他统计信息。
      • 数据库级别操作：例如，生成某个表的总更新计数，或者在数据更新时触发清理缓存等操作。

    • 行级触发器，触发动作只发生1000次；

      通常用于：

      • 逐行记录详细变化：当我们需要对表中每一行的数据变化进行处理或记录时，使用行级触发器。它可以针对每一行执行，记录每一行的修改前后状态。
      • 审计和追踪：
        • 当我们需要对数据进行审计时，逐行记录每一行的变化是非常重要的。行级触发器可以帮助你记录所有数据变动的历史记录，保证数据变动的可追溯性。
        • 例子：对表中的每一行进行审核或记录其变更，确保每个更新都有明确的记录和责任追溯。
      • 逐行计算或验证：
        • 需要对每一行的数据进行单独的验证或计算时，行级触发器是最合适的选择。例如，更新某个表时，需要根据每行数据执行复杂的验证或计算逻辑。
        • 例子：每当更新Employee表时，检查每个员工的薪资是否满足一定条件，如果不满足，则阻止更新。

  6. 触发条件

  • 由WHEN子句指定（MySQL使用IF）
    只有当触发条件为真，触发操作才会执行。
  • 省略WHEN触发条件，触发操作体在触发器激活后立即执行。

  1. 触发动作体

    触发动作体是满足触发器条件后，执行的一系列数据库操作。

    • 如果触发操作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不会发生任何变化。这就是rollback

  2. 引用（MySQL不支持）

    REFERENCING NEW | OLD ROW AS <变量>

    通常用于：

    • 行级触发器
    • 在触发器体中使用NEW和OLD引用事件之后的新值和事件之前的旧值。

    REFERENCING NEW | OLD TABLE AS <变量>

    通常用于

    • 语句级触发器
    • 仅适用于AFTER触发器。

【例】将每次对表 Student 的插入操作所增加的学生个数记录到表 StudentInsertLog 中。

CREATE TRIGGER Student_Count
AFTER INSERT ON Student
FOR EACH STATEMENT
BEGIN
    DECLARE before_count INT;
    DECLARE after_count INT;

    -- 获取插入前的学生数量
    SELECT COUNT(*) INTO before_count FROM OLD;

    -- 获取插入后的学生数量
    SELECT COUNT(*) INTO after_count FROM NEW;

    -- 计算新增的学生数量，并记录到StudentInsertLog表
    INSERT INTO StudentInsertLog (Numbers)
    VALUES (after_count - before_count);
END;
  • DECLARE 是用来声明局部变量的关键字。通常，DECLARE 在 存储过程、触发器、函数 或 块语句 中使用，用来声明在代码块内部使用的变量。

【例】定义一个 BEFORE 行级触发器，为教师表 Teacher 定义完整性规则 “教师的工资不得低于 4000 元，如果低于 4000，自动改为 4000 元”。

CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
FOR EACH ROW
BEGIN
	IF (NEW.Job = '教师') AND (NEW.Sal < 4000)
	THEN
		NEW.Sal := 4000;-- MySQL: SET NEW.Sal = 4000
	END IF;
END;

【例】创建触发器 credits_earned，当 takes 表中的 grade 被更新后触发，引用新行作为 nrow，引用旧行作为 orow，并在每一行执行时更新学生的学分。

CREATE TRIGGER credit_earned
AFTER UPDATE OF Grade ON takes
REFERENCES NEW ROW AS nrow
REFERENCES OLD ROW AS orow
FOR EACH ROW
WHEN (nrow.grade != 'F' AND nrow.Grade IS NOT NULL
     AND (orow.grade = 'F' OR orow.grade IS NULL))
BEGIN
	UPDATE Student
	SET tot_cred = tot_cred +
    (SELECT credits
    FROM course
    WHERE course.course_id = nrow.course_id)
    WHERE student.id = nrow.id;
END;

使用触发器

  • 触发器的执行，是由触发事件激活的，并由数据库服务器自动执行。

  • 一个数据表上可能定义了多个触发器，同一个表上的多个触发器，激活时遵循如下的执行顺序：

    1. 执行该表上的 BEFORE 触发器；
    2. 激活触发器的 SQL 语句（触发事件）；
    3. 执行该表上的 AFTER 触发器。

  • 注意：如果激活触发器的 SQL 语句违反了约束条件，则不会执行 AFTER 触发器。

  • 存在级联触发问题

删除触发器

  • DROP TRIGGER <触发器名> ON <表名>
  • 触发器的删除不影响数据表和数据
  • 删除表会自动删除其上的所有触发器
  • 表的所有者拥有删除权限

7.6 SQL Server 完整性的实现

  • 实际的数据库产品对完整性的支持不尽相同
  • SQL Server 提供了对实体完整性、参照完整性和用户定义的完整性的支持

  • 实体完整性的实现

    • 索引
    • UNIQUE 约束
    • PRIMARY KEY 约束
    • IDENTITY 属性

  • 参照完整性的实现

    • FOREIGN KEY 约束
    • CHECK 约束
    • 触发器

  • 用户定义的完整性的实现

    • CREATE TABLE 中的列级和表级约束：
      • CHECK 约束
      • DEFAULT 约束
      • NOT NULL 约束
      • 规则
    • 存储过程
    • 触发器

  • SQL Server 用于维护数据完整性的对象：

    • 约束
    • 规则
    • 默认值
    • 触发器

  • 默认值和规则对象：

    • 是可以绑定到一个或多个列或用户定义数据类型的对象
    • 可以一次定义，多次使用
    • 非标准内容
    • 可以用 DEFAULT 约束和 CHECK 约束代替

  • 创建默认值和规则的示例：

    • 创建一个大于 0 的规则，并绑定到产品表的单价字段上：

      CREATE RULE R_jg AS @jg > 0;
sp_bindrule 'R_jg', '产品.单价';

      其中的 @jg 是 变量名，在 SQL Server 中用于定义 规则（Rule） 时，必须使用一个以 @ 开头的占位符变量，它代表将来要绑定的列值。

      sp_bindrule的语法与解释

      sp_bindrule [ @rulename = ] 'rule_name',
             [ @objname = ] 'object_or_uddt_name'
      参数名 说明
      @rulename 要绑定的规则名称（你定义的 RULE 名）
      @objname 要绑定的列名（格式为 '表名.列名'）或用户自定义数据类型名

    • 创建一个默认值为 100081 的对象，并将其绑定到 Student 表的 Zipcode 字段上：

      CREATE DEFAULT DF_zip_code AS 100081;
sp_binddefault 'DF_zip_code', 'Student.zipcode';

SQL Server 的数据完整性对象 —— 约束

  • Constraints：定义关于列中允许值的规则

  • 是强制完整性的标准机制

  • 使用约束（Constraints）优于使用触发器、规则和默认值

  • SQL Server 提供多种强制列中数据完整性的机制：

    • PRIMARY KEY 约束
    • FOREIGN KEY 约束
    • UNIQUE 约束
    • CHECK 约束
    • DEFAULT 约束
    • NOT NULL 约束

  • 各种完整性类型与约束类型对照表

image-20250606145901088

CHECK 约束

  • 可以将列的取值限定在指定范围内；
  • 可以引用同一表中的其他列，约束同一个表中多个列之间的取值关系；
  • 当执行 INSERT 语句或者 UPDATE 语句时，CHECK 约束将验证数据值；
  • CHECK 约束中不能包含子查询。

示例：

ALTER TABLE Employees
ADD CONSTRAINT CK_birthdate
CHECK (BirthDate > '01-01-1900' AND BirthDate < getdate());

PRIMARY KEY 约束

  • 表示唯一标识表中的行的列或列集；
  • 可以在创建表时创建，也可以在随后添加、修改或删除；
  • 必须非空且没有重复值；
  • SQL Server 自动创建唯一的索引来强制 PRIMARY KEY 约束所要求的唯一性；

示例

ALTER TABLE Customers
ADD CONSTRAINT PK_Customers
PRIMARY KEY (CustomerID);

【例】使用 SQL 语句建立主键约束

CREATE TABLE publishers (
    pub_id   char(4) NOT NULL
             PRIMARY KEY
             CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') 
                    OR pub_id LIKE '99[0-9][0-9]'),
    pub_name varchar(40),
    city     varchar(20),
    state    char(2) NULL,
    country  varchar(30)
);

FOREIGN KEY 约束

  • 外键所引用的列必须是有 PRIMARY KEY 约束或 UNIQUE 约束的列
  • 列的取值范围只能是被引用列的取值或空值
  • FOREIGN KEY 约束可以引用其他表
  • FOREIGN KEY 可以是单列键或者是多列键

示例

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

  • 使用 FOREIGN KEY 约束时，应该考虑以下几个因素：

    • 主键和外键的数据类型必须严格匹配；

    • 外键约束不能自动创建索引，需要用户手动创建；

      • 外键字段默认不会被自动索引；
      • 如果需要提高性能（如加速连接、更新、删除操作），应当手动为外键字段创建索引。

      会自动创建索引的约束：PRIMARY KEY、UNIQUE

      不会自动创建索引的约束：FOREIGN KEY、CHECK、NOT NULL

    • 用户想要修改外键约束的数据，必须有对外键约束所参考表的 SELECT 权限或者 REFERENCES 权限；

    • 一个表可以有多个 FOREIGN KEY 约束，最多可以有 31 个外键约束；

    • 在临时表中，不能使用外键约束。

DEFAULT 约束

  • 用于提供列的默认值；
  • 默认值可以是任何取值为常数的表达式，如：常量、NULL、系统函数、数学表达式等；
  • 默认值必须符合此列上的任何 CHECK 约束；
  • 在使用 INSERT 语句时，如果没有提供值，则 DEFAULT 约束会指定该列中使用的值；
  • 只有在表中插入数据时，系统才检查 DEFAULT 约束。

示例

ALTER TABLE Customers
ADD CONSTRAINT DF_contactname
DEFAULT 'UNKNOWN'
FOR ContactName;

UNIQUE 约束

  • 用于限制一个列中不能有非空的重复值

  • SQL Server 自动创建 UNIQUE 索引来强制 UNIQUE 约束的唯一性要求

  • 不能对现有的包含重复值的列添加 UNIQUE 约束

  • 注意 UNIQUE 约束与 PRIMARY KEY 约束的区别

    约束类型 唯一性要求 是否允许 NULL 每表允许个数
    PRIMARY KEY 必须唯一 不允许 NULL 每表仅允许 1 个
    UNIQUE 必须唯一 允许 NULL 每表可多个

示例：

ALTER TABLE Suppliers
ADD CONSTRAINT U_CompanyName
UNIQUE (CompanyName);
  • 当使用唯一性约束时，需要考虑以下几个因素：
    • 使用唯一性约束的字段允许为空值
    • 一个表中可以允许有多个唯一性约束
    • 可以把唯一性约束定义在多个字段上
    • 唯一性约束用于强制在指定字段上创建一个唯一性索引
    • 缺省情况下，创建的索引类型为非聚集索引

NOT NULL 约束

  • 指定一列不允许空值
  • NULL 的存在表明值未知或未定义

示例：

CREATE TABLE SC (
    Sno   CHAR(9) NOT NULL,
    Cno   CHAR(4) NOT NULL,
    Grade SMALLINT CHECK (Grade >= 0 AND Grade <= 100),
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

使用约束的注意事项

  • CREATE TABLE：在创建表时创建约束
  • ALTER TABLE：在一个已有的表上创建约束
  • 可添加单列或多列约束：
    • 若约束应用于单列，称为列级约束；
    • 若约束引用了多列，称为表级约束。
  • 可直接在表上创建、修改和删除约束，而不必删除并重建表
  • 当给一个表添加约束的时候，SQL Server 将检查现有数据是否违反约束
  • 建议创建约束时指定名称，否则系统将为约束自动产生一个复杂的名称

SQL Server 的触发器

  • 用途

    • 定义用户定制的错误信息

      • 通过使用触发器，可以在特定条件出现时调用预定义或动态定义的定制错误信息
      • 约束、规则和默认只能通过标准系统错误信息来表达错误。若需要定制信息或更复杂的错误处理，需使用触发器

    • 实现用户定义的完整性

    • 在数据库中的相关表上实现级联更改

    • 实现比声明式约束更复杂的业务规则

    • 维护非标准数据，特别是处理较为复杂的逻辑

  • SQL Server 的触发器定义

CREATE TRIGGER <触发器名>
ON <表名> | <视图名>
{FOR | AFTER | INSTEAD OF} <触发事件>
AS
<触发动作体>

【例1】创建限制更新数据的触发器，限制将 SC 表中不及格学生的成绩改为及格。

【例2】创建删除触发器，当删除一本书时，先检查这本书是否已经被卖过，即是否和订单关联，如果已经有关联则该书的信息不能被删除，删除动作需回滚。

【例1】
CREATE TRIGGER tri_grade
ON SC FOR UPDATE
AS
    IF UPDATE (Grade)
        IF EXISTS (
            SELECT * 
            FROM INSERTED 
            JOIN DELETED 
              ON INSERTED.Sno = DELETED.Sno
            WHERE INSERTED.Grade >= 60 
              AND DELETED.Grade < 60
        )
        BEGIN
            RAISERROR ('不许将不及格的成绩改为及格！', 16, 1)
            ROLLBACK
        END

  • INSERTED 表和 DELETED 表

    SQL Server 触发器专用的临时虚拟表，由 SQL Server 自动管理

    • NSERTED 表
      • 保存 INSERT 操作中新插入的数据，或 UPDATE 操作中更新后的数据，即：插入到触发表的新数据行的副本。
    • DELETED 表
      • 存放 DELETE 操作中删除的数据，或 UPDATE 操作中更新前的数据，即：从触发表中删除的旧数据行的副本。
    • 触发操作完成后，与触发器相关的临时表会自动删除。

  • 对带触发器的表的操作

    • INSERT 操作时，新插入的数据被记录在 INSERTED 表中。
    • DELETE 操作时，删除的数据被记录在 DELETED 表中。
    • UPDATE 操作时，相当于先执行删除操作，再执行插入操作，即：
      • 先在触发表中删除将被更新的行，并将这些行复制到 DELETED 表中；
      • 然后将更新后的新行复制到触发表和 INSERTED 表中。
    • 触发器中对 INSERTED 表和 DELETED 表的使用同普通表一样。
    • 可以通过 INSERTED 表和 DELETED 表所记录的数据，判断对数据的修改是否正确。

  • ROLLBACK

    ROLLBACK 的作用是：一旦触发某个错误或业务规则被违反，就放弃整个操作，数据不作任何修改。

【例2】
CREATE TRIGGER Products_Delete
ON Products FOR DELETE
AS
    IF (
        SELECT COUNT(*)
        FROM [Order Details]
        INNER JOIN deleted
        ON [Order Details].ProductID = deleted.ProductID
    ) > 0
    BEGIN
        RAISERROR('该产品有定购历史，事务无法进行！', 10, 1)
        ROLLBACK TRANSACTION
    END

Oracle触发器示例

阻止用户把工资从高调为低（防止降薪），否则抛出自定义错误 -20003。

create trigger teacher_sal           -- 触发器名字
before update of salary on teacher   -- 作用表
referencing new x, old y             -- 定义更新前后的值
for each row
when (x.salary < y.salary)           -- 对每一条记录都检查
begin
    -- 如果违反则执行，提示无效更新
    raise_application_error(-20003, 'invalid salary on update');
end;

PostgreSQL示例

-- 创建一个触发器函数
CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查工资是否为正数
    IF NEW.salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be a positive number';
    END IF;
    -- 返回 NEW 表示继续执行插入操作
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建 BEFORE INSERT 触发器
CREATE TRIGGER before_insert_employees
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();
  • 在 PostgreSQL 中，$$ 是一种 字符串定界符（delimiter），用于包裹函数体的代码块，避免和里面的单引号冲突。

## 触发器与 CHECK 约束

  • CHECK 约束只能根据逻辑表达式或同一表中的另一列来验证列值。
    • 如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器（看实际系统的情况）。
  • 约束只能通过标准的系统错误提示传递错误信息。
    • 如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器。
  • 触发器可以引用其它表中的列。
  • 触发器可以支持约束的所有功能；但并不总是最好的方法，因为触发器的系统开销更大。

使用何种完整性强制方法？

  • 应综合考虑功能和性能开销，来决定使用何种强制数据完整性的方法：

    • 对于基本的完整性逻辑，例如有效值和维护表间的联系，最好使用声明式完整性约束；

    • 如果要维护复杂的、大量的、非主键或外键关系的部分数据，使用触发器或存储过程；

    • 约束比较简单，开销低，适合于完整性逻辑比较简单的场合；

    • 触发器比较复杂，开销大，适合于完整性逻辑比较复杂的场合。

本章小结

  • 数据库的完整性是为了保证数据库中存储的数据的正确性、有效性和相容性。
  • RDBMS完整性实现的机制
    • 完整性约束定义机制
    • 完整性检查机制
    • 违背完整性条件时RDBMS应采取的额动作
  • 实体完整性
  • 参照完整性
  • 用户定义的完整性
  • 实现数据库完整性的一个重要方法——触发器

PostgreSQL示例

-- 创建一个触发器函数
CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查工资是否为正数
    IF NEW.salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be a positive number';
    END IF;
    -- 返回 NEW 表示继续执行插入操作
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建 BEFORE INSERT 触发器
CREATE TRIGGER before_insert_employees
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();
  • 在 PostgreSQL 中，$$ 是一种 字符串定界符（delimiter），用于包裹函数体的代码块，避免和里面的单引号冲突。

## 触发器与 CHECK 约束

  • CHECK 约束只能根据逻辑表达式或同一表中的另一列来验证列值。
    • 如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器（看实际系统的情况）。
  • 约束只能通过标准的系统错误提示传递错误信息。
    • 如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器。
  • 触发器可以引用其它表中的列。
  • 触发器可以支持约束的所有功能；但并不总是最好的方法，因为触发器的系统开销更大。

使用何种完整性强制方法？

  • 应综合考虑功能和性能开销，来决定使用何种强制数据完整性的方法：

    • 对于基本的完整性逻辑，例如有效值和维护表间的联系，最好使用声明式完整性约束；

    • 如果要维护复杂的、大量的、非主键或外键关系的部分数据，使用触发器或存储过程；

    • 约束比较简单，开销低，适合于完整性逻辑比较简单的场合；

    • 触发器比较复杂，开销大，适合于完整性逻辑比较复杂的场合。

本章小结

  • 数据库的完整性是为了保证数据库中存储的数据的正确性、有效性和相容性。
  • RDBMS完整性实现的机制
    • 完整性约束定义机制
    • 完整性检查机制
    • 违背完整性条件时RDBMS应采取的额动作
  • 实体完整性
  • 参照完整性
  • 用户定义的完整性
  • 实现数据库完整性的一个重要方法——触发器

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布