05-数据库完整性

基本概念

数据库完整性
- 数据的正确性：数据符合现实语义
- 数据的相容性：同一对象在不同表中表示符合逻辑
完整性机制
- 定义完整性约束条件的机制
- 完整性检查的方法
- 违约处理

完整性：防范不合语义、不正确的数据
安全性：防范非法用户和非法操作

实体完整性（定义主键）

单个属性为主键：可在列/表级定义

-- 在列级定义
CREATE TABLE student (
    sno INT PRIMARY KEY,
    sname VARCHAR(20),
    sage INT
);
-- 在表级定义
CREATE TABLE student (
    sno INT,
    sname VARCHAR(20),
    sage INT,
    PRIMARY KEY (sno)
);

多个属性为主键：只能在表级定义

CREATE TABLE student (
    sno INT,
    sname VARCHAR(20),
    sage INT,
    PRIMARY KEY (sno, sname)
);

检查实体完整性

检查
- 主键是否唯一
- 主键的各个属性是否非空
检查手段
- 全表遍历： $O(n)$
- B+ 树索引： $O(\log n)$

参照完整性（定义外键）

FOREIGN KEY：定义外键的列
REFERENCES：定义外键引用的表和列（必须是唯一的）

CREATE TABLE student (
    sno INT PRIMARY KEY,
    sname VARCHAR(20),
    sage INT
);
CREATE TABLE course (
    cno INT PRIMARY KEY,
    cname VARCHAR(20),
    sno INT,
    FOREIGN KEY (sno) REFERENCES student(sno)
);

检查参照完整性

操作

触发条件

触发操作

插入违反外键约束

插入新记录时，外键值在被引用的表中不存在

拒绝插入

更新违反外键约束

更新外键值时，外键值在被引用的表中不存在

拒绝更新

删除被引用的主键记录

删除主键记录时，引用表中仍有使用该外键值的记录

拒绝删除/级联删除/置空

更新被引用的主键记录

更新主键记录时，引用表中仍有使用该外键值的记录

拒绝更新/级联更新/置空

违约处理语法

NO ACTION：拒绝操作，默认
CASCADE：级联操作，删除/更新主键时，删除/更新引用表中所有外键值相同的记录
SET NULL：设置为空

...
    FOREIGN KEY (sno) REFERENCES student(sno) 
      ON UPDATE CASCADE
      ON DELETE SET NULL
...

用户定义的完整性（语义要求）

单个属性的取值条件
- UNIQUE：唯一性约束
- NOT NULL：非空约束
- CHECK：取值范围约束
元组的取值条件：属性之间存在依赖，需要约束

CREATE TABLE student(
    sno UNIQUE,
    sname NOT NULL,
    ssex CHAR(1) CHECK (ssex IN ('M', 'F')),
    CHECK(ssex='F' OR sname NOT LIKE 'Ms.%'),
)

完整性约束命名子句

CONSTRAINT：为约束规则显式命名，便于后续更改/删除
不用也能正常定义规则，但是为修改和维护带来不便

CREATE TABLE student(
    sno INT,
    sname VARCHAR(20) CONSTRAINT name_check NOT NULL,
    ssex CHAR(1) CONSTRAINT sex_check CHECK (ssex IN ('M', 'F')),
    CONSTRAINT name_sex_check CHECK(ssex='F' OR sname NOT LIKE 'Ms.%'),
);
-- 修改约束
ALTER TABLE student
    DROP CONSTRAINT name_check;
ALTER TABLE student
    ADD CONSTRAINT name_check UNIQUE (sname);

断言

若断言为假，拒绝执行
性能开销较大

CREATE ASSERTION max_enrollment_check
    CHECK (60 >= (SELECT COUNT(*) 
      FROM course,sc 
      WHERE sc.cno = course.cno AND course.name='DB'));

触发器

Event-Condition-Action：当特定事件发生，且满足触发条件时，激活触发器，执行定义的操作
保存在数据库服务器中
表的拥有者才可以在表上创建触发器
触发器名
- 同一模式下必须唯一
- 触发器名称和表名称必须在同一模式下
表名：触发器只能定义在基本表上
触发事件
- INSERT UPDATE DELETE
- UPDATE OF <column_name>：只在指定列上触发
激活顺序：BEFORE触发器、SQL 语句、AFTER触发器
执行频率
- FOR EACH ROW：每行触发一次
- FOR EACH STATEMENT：每个语句触发一次
动作：可以是
- SQL 语句
- PL/SQL 语句块
触发器的删除者必须有对应的权限

CREATE TRIGGER <trigger_name>
  {BEFORE | AFTER} <event> ON <table_name>
  REFERENCING {OLD | NEW} ROW AS <alias>
  FOR EACH {ROW|CONDITION}
  [WHEN <condition>] <action>
DROP TRIGGER <trigger_name> ON <table_name>;

CREATE TRIGGER Insert_Or_Update_Sal 
BEFORE INSERT OR UPDATE ON Teacher  
REFERENCING NEW row AS newTuple
FOR EACH ROW
  BEGIN
    IF (newTuple.Job='教授') AND (newTuple.Sal < 4000) 
      THEN newTuple.Sal :=4000;                
    END IF;
  END;
-- 删除触发器
DROP TRIGGER Insert_Or_Update_Sal ON Teacher;

上一页04-数据库安全性下一页06-关系范式

最后更新于8天前