7 完整性约束

7.1 完整性约束

  • 什么是数据库的完整性
    • 数据的正确性和相容性
    • 防止不合语义的数据进入数据库。
完整性控制机制
  • 完整性约束条件定义机制
    • 完整性约束条件：数据模型的组成部分，约束数据库中数据的语义
    • DBMS应提供定义数据库完整性约束条件的方法
  • 完整性检查机制
    • 检查用户发出的操作请求是否违背了完整性约束条件
  • 违约反应
    • 如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性
完整性约束条件

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

    • 列：对属性的取值类型、范围、精度等的约束条件
    • 元组：对元组中各个属性列间的联系的约束
    • 关系：对若干元组间、关系集合上以及关系之间的联系的约束

  • 对象的两种状态：

    • 静态：对静态对象的约束是反映数据库状态合理性的约束
    • 动态：对动态对象的约束是反映数据库状态变迁的约束。涉及新值和旧值

  • 分类：3*2

  • 静态列级约束

    • 对列的取值域的说明
    • 如取值类型、取值范围、是否为空值等约束

  • 静态元组约束

    • 规定元组的各个列之间的约束关系
    • 如有订货关系（ID，商品，订货量，发货量），要求每条记录中：发货量<=订货量
    • 用 Check 约束实现

  • 静态关系约束

    • 关系的各个元组之间或若干关系之间存在的各种联系或约束
    • 分类：
      • 实体完整性约束
      • 参照完整性约束
      • 函数依赖约束
      • 统计约束
    • 需用触发器实现
    • 例
      工资表（职工ID，，工资，是否部门经理），要求：
      职工平均工资的2倍<=部门经理的工资<=职工平均工资的5倍

  • 动态列级约束

    • 动态列级约束是修改列定义或列值时应满足的约束条件
    • 修改列定义时的约束
      例：将原来允许空值的列改为不允许空值时:
      该列目前已存在空值，则拒绝这种修改（数据库自动实现）
    • 修改列值时的约束
      修改列值时新旧值之间要满足的约束条件（使用触发器实现）
      例：职工工资调整 >= 原来工资
      年龄只能增长

  • 动态元组约束
    修改元组值: 各个字段之间要满足的约束条件
    例: 职工工资调整不得低于其原来工资 + 工龄*1.5
    用触发器实现

  • 动态关系约束
    对关系变化前后状态的限制条件
    例：事务一致性、原子性等约束条件
    具体内容在事务处理部分讲解

完整性约束另一种分类方法
  • 最重要的几类完整性约束条件：
    • 域完整性
    • 实体完整性约束
    • 参照完整性约束
      可以方便地在数据库中设置
  • 其它的完整性约束条件：
    • 用户自定义完整性约束
      往往需编程实现（触发器或Check等）

7.2 完整性控制

  • 完整性规则五元组表示：(D，O，A，C，P)

    • D（Data） 约束作用的数据对象；
    • O（Operation） 触发完整性检查的数据库操作
      当用户发出什么操作请求时需要检查该完整性规则
      是立即检查还是延迟检查；
    • A（Assertion） 数据对象必须满足的断言或语义约束，是规则的主体；
    • C（Condition） 选择A作用的数据对象值的谓词（insert/modify/...）
    • P（Procedure） 违反完整性规则时触发的过程。

    例：在“学号不能为空”的约束中
    D 约束作用的对象为Sno属性
    O 插入或修改Student 元组时
    A Sno不能为空
    C 无（A可作用于所有记录的Sno属性）
    P 拒绝执行该操作

7.3 违约反应

  • 违反参照完整性的操作的违约反应：
    • （通常）拒绝执行
    • 接受这个操作，同时执行一些附加的操作，以保证数据库的状态正确
参照完整性违约反应
  • 外键是否可以取空值：依赖应用环境语义而定
  • 在被参照关系中删除元组时，若参照关系有若干元组的外键值与被删除的被参照关系的主键值相同，此时的违约反应可以是：
    删掉21系
    • 级联删除（CASCADES）：一起也都给删了
      21系的所有人都被删了
    • 受限删除（RESTRICTED）：删一部分
    • 置空值删除（NULLIFIES）：相关的值置空
      21系所有人的系号置为空值
  • 在参照关系中插入元组时，若被参照关系不存在相应的元组，则违约反应可以是：
    插入一个60系的同学（buaa目前没有60系）
    • 受限插入
    • 递归插入
      建60系
  • 修改被参照关系中的主键，若主键在参照关系中已经被引用，则违约反应可以是：
    • 级联修改
    • 受限修改
    • 置空值修改

7.4 用户自定义约束的实现

7.4.1 Check约束

  • CHECK 约束通过限制输入到列中的值来强制域的完整性。
  • 常用于实现静态列级约束和静态元组约束。
  • 可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的逻辑（布尔）表达式来创建 CHECK 约束。
    表达式可包含列名、比较运算符（>、<、 =等）、逻辑运算符(and、or等)、条件谓词（in、like等），可使用pattern（如_、%通配符，[]选择符等）

欲将 salary 列的取值范围限制在 $15,000 至 $100,000 之间，则Check约束为：
salary >= 15000 AND salary <= 100000

检查Email字段的格式：
email like ‘%@%._%’

学号是一个五位字符串，前二位为字符，后三位为数字，则可定义CHECK约束如下：
SID like ‘__[0,9][0,9][0,9]’

7.4.2 触发器

  • 用于实现复杂逻辑的用户自定义约束的工具

  • 是一种特殊的存储过程

    • 与表（或视图、数据库等）紧密相连，不能脱离宿主存在
    • 由数据库自动调用执行，用户不能调用
    • 没有参数和返回值

  • 是一种Event-Condition-Action规则的实现

    • Event事件 : 引起数据库更新的事件，如执行Insert语句向表中插入数据
    • Condition条件 : 判断是否满足条件的SQL表达式
    • Action动作 : 任意的SQL语句
CREATE [ OR REPLACE ] TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF]
trigger_event ON table_reference
[FOR EACH ROW [ WHEN trigger_condition ] ]
trigger_body;
例
  • Fruits ( Fname, Price ); Sells ( Cname, Fname, Stime, Quantity )
  • 需求： 当向Sell表中插入一条新的购买记录的时候，若Fname（水果名称）在fruits表中不存在，则向Fruits表中插入一条新的记录，其中Fname为新购买记录中的水果名称，Price为NULL。
    分析： 可以用外键约束的级联插入实现。但是很多数据库不支持级联插入，怎么办？
    用触发器实现
    Pasted image 20250418103208.png
    Pasted image 20250418105924.png
触发器
  • 事件 INSERT
    • 表/视图级事件：INSERT / DELETE / UPDATE
    • 数据库级事件：CREATE / ALTER / DROP TABLE /PROCEDURE / VIEW…
  • 触发的时机 AFTER
    • AFTER /BEFORE /INSTEDA OF
    • Instead Of 触发器会替换掉用户提交的操作语句。常用于不可更新视图的更新。
      想给所有课的平均分都+1分，但是加不到同学成绩上，只能加到平均分上。可以用Instead of来代替给所有学生的所有课+1分
  • 触发/执行粒度 FOR EACH ROW
    • 语句级触发器：缺省
      每条用户语句触发一次
    • 行级触发器： FOR EACH ROW
      对于表中每条受影响记录，触发一次触发器
      用户语句：Update stu set Gender=‘F’ --500条语句受影响
      语句级触发器：触发和执行一次
      行级触发器：触发和执行500次
  • Referencing引用 REFERENCING NEW ROW AS NewTuple
    • 名为“Inserted”的表，包含新插入的行
    • 名为“Deleted” 的表，包含删除的行
    • 在触发器业务逻辑中可以通过引用来处理新值、旧值
    • OpenGauss、MySQL等数据库中可以直接使用关键字New、Old来引用新、旧值：New.Attribute,Old.Attribute
  • 动作 INSERT INTO Fruits(Fname) VALUES(NewTuple.Fname)
    • 实现自定义约束的业务逻辑
    • 通常用于进行关联数据更新，不能返回查询结果
    • 若需多于一条语句，则可用程序块实现：Begin…End
    • 拒绝更新的违约动作：引发系统错误，提示数据库拒绝更新，可用RaiseError（SQLServer）、Signal（MySQL）、Raise（OpenGauss）函数

Pasted image 20250418105648.png
Pasted image 20250418105715.png
Pasted image 20250418105729.png

Built with MDFriday ❤️