# 数据库约束（Database constriants）

# 完整性约束 (Intergral constraint)

概要

完整性约束指定：完整性约束是在数据库架构设计（schema design）过程中被定义的。数据库设计者负责确保定义的完整性约束之间不会互相冲突。
⾃动化检测：虽然检测完整性约束的过程可以⾃动化，但这可能会引⼊不可接受的性能开销。
完整性约束检查：完整性约束在数据库被修改时进⾏检查。可以指定检查完整性约束的时机，⽐如在⼀个 SQL 语句执⾏后或⼀个事务（transaction）结束时。
- 事务：⼀个事务是⼀组 SQL 语句，它们作为⼀个单⼀的、不可分割的操作执⾏，这个概念也被称为原⼦性（atomicity）。
违反完整性约束的反应：幻灯⽚还提到了如果违反了完整性约束，可以采取的⼏种响应措施：
1. 拒绝数据库操作：不执⾏违反完整性约束的操作。
2. 中⽌整个事务：如果事务中的任何部分违反了完整性约束，就回滚（rollback）事务中的所有操作。
3. 执⾏ “维护” 操作：如果完整性约束被违反，执⾏必要的操作以恢复数据库到合法状态。

# 事务（Transaction）

# 事务的关键特性 (ACID)：

ACID 是事务的四个关键属性，分别是原子性 (Atomicity)、一致性 (Consistency)、隔离性 (Isolation) 和持久性 (Durability)。

# 1. 原子性 (Atomicity)：

定义：事务中的所有操作要么全部执行成功，要么全部失败回滚。事务不能部分执行。
示例：假设你进行一个银行转账操作，转账事务包含两个步骤：从账户 A 扣钱，从账户 B 加钱。原子性确保如果其中一个步骤失败，整个转账操作会被回滚，避免账户 A 扣钱但账户 B 没有加钱的情况。

# 2. 一致性 (Consistency)：

定义：事务执行前后，数据库必须保持一致性状态，确保数据库从一个合法的状态转换到另一个合法的状态。
示例：在银行转账的例子中，一致性确保无论事务成功或失败，账户 A 和账户 B 的总金额不会因为转账事务而改变。

# 3. 隔离性 (Isolation)：

定义：多个事务同时执行时，一个事务的中间状态对其他事务是不可见的。隔离性保证并发事务之间互不干扰，避免数据不一致。
示例：如果两个用户同时从一个账户中取钱，隔离性保证每个用户在取钱时不会看到对方的操作结果，以避免超取。

SQL 中有四种常见的事务隔离级别，分别是：

Read Uncommitted：允许读取其他未提交事务的数据，可能导致脏读（Dirty Read）。
Read Committed：只能读取其他事务已提交的数据，避免脏读。
Repeatable Read：确保同一个事务在读取数据时，数据在整个事务过程中不会被修改，避免不可重复读。
Serializable：事务被严格隔离，避免所有并发事务问题，通常开销较大。

# 4. 持久性 (Durability)：

定义：一旦事务提交成功，其对数据库的修改就是永久性的，系统即使发生崩溃，也不会丢失已提交的事务结果。
示例：在银行转账完成并提交后，即使服务器崩溃，转账的记录仍然会保存在数据库中，不会丢失。

# 事务的生命周期：

事务通常包括以下几个阶段：

开始 (Begin)：
- 通过 BEGIN 语句显式开始一个事务。
- 也可以隐式开始（比如直接执行 INSERT 等操作）。
执行操作 (Perform Operations)：
- 事务包含一组操作，如插入、更新、删除等 SQL 语句。
提交 (Commit)：
- COMMIT 语句将事务的所有操作永久写入数据库。
- 一旦提交，事务的更改就无法回滚。
回滚 (Rollback)：
- 如果事务中的某个操作失败，或者用户手动调用 ROLLBACK ，事务将被回滚，所有对数据库的修改将被撤销。

示例：一个简单的事务
以下是一个 SQL 中事务的基本示例，涉及对数据库的修改和回滚：

row

BEGIN;  -- 开始事务

-- 插入一条记录
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);

-- 更新账户余额
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;

-- 提交事务
COMMIT;

在这个事务中，我们首先插入一个新账户，并将账户余额减少了 200。如果在任何步骤中发生错误（如账户不存在或余额不足），我们可以使用 ROLLBACK 撤销事务的所有操作。

row

BEGIN;  -- 开始事务

-- 执行一些操作
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;

-- 如果某个操作失败，则回滚事务
ROLLBACK;

# 常见的事务问题：

由于事务在并发情况下会相互影响，以下是一些常见的并发问题：

脏读 (Dirty Read)：
- 一个事务读取了另一个尚未提交的事务修改的数据，可能会导致数据不一致。
不可重复读 (Non-repeatable Read)：
- 一个事务在多次读取同一数据时，数据可能已经被另一个事务修改，导致读取到不同的结果。
幻读 (Phantom Read)：
- 一个事务在多次查询时，发现另一个事务插入了新数据，导致每次查询的结果集不同。

通过设置适当的隔离级别，可以避免这些并发问题。

# 断言 (assertions)

Assertion 是一种谓词，表示数据库中的某个条件必须始终为真。它们用于确保某些业务规则始终在数据库中得到遵守。例如，假设我们有一条规则：“每个分支的贷款总额必须小于该分支的账户余额总和”。这种规则涉及多个表的约束，无法通过单一表的约束（如 CHECK 或 FOREIGN KEY ）来表达，因此需要使用 ASSERTION 。
创建 assertion

row

1	CREATE ASSERTION assertion_name CHECK (condition);

其中， assertion_name 是 Assertion 的名称， condition 是希望数据库始终满足的条件。每当数据库的状态发生改变时，数据库管理系统（DBMS）会检查该 Assertion 是否仍然成立。

例子 1 为了确保每个分支的贷款总额小于该分支的账户余额总和

row

-- 该 Assertion 确保在任意分支中，不会出现贷款总额大于或等于账户余额总和的情况 。
CREATE ASSERTION sum_constraint CHECK (
  NOT EXISTS (
    SELECT *
    FROM branch
    WHERE (
      SELECT SUM(amount) FROM loan WHERE loan.branch_name = branch.branch_name
    ) >= (
      SELECT SUM(amount) FROM account WHERE account.branch_name = branch.branch_name
    )
  )
);

# 例子 2 如果我们希望确保每个贷款都有至少一个借款人，并且该借款人拥有余额不少于 1000 美元的账户，可以创建以下 Assertion：

row

-- 该 Assertion 确保每笔贷款都满足至少有一个借款人拥有余额不少于 1000 美元的账户 。
CREATE ASSERTION balance_constraint CHECK (
  NOT EXISTS (
    SELECT *
    FROM loan
    WHERE NOT EXISTS (
      SELECT 1
      FROM borrower JOIN depositor ON borrower.customer_name = depositor.customer_name
      JOIN account ON depositor.account_number = account.account_number
      WHERE loan.loan_number = borrower.loan_number
      AND account.balance >= 1000
    )
  )
);

# 静态完整性约束 (Static Integrity Constraints)

静态完整性约束是指对数据库在某一时刻的状态进行限制，确保每个时刻的数据都是合法的、符合规则的。它们定义了每个合法的数据库状态必须满足的条件。如果数据库状态违反了这些约束，则被视为非法操作。以下是主要的静态完整性约束类型。

# 1. 域约束 (Domain Constraints)

域约束确保每个列的值符合预定义的数据类型和范围。例如，一个人的年龄应该是正整数，不能为负值或超出合理范围。

示例：

row

CREATE TABLE Student (
    student_id INTEGER NOT NULL,
    name VARCHAR(50),
    age INTEGER CHECK (age >= 0)
);

在此表中， age 列通过 CHECK 约束，确保所有插入的值都必须为非负整数。

# 2. 键约束 (Key Constraints)

键约束确保数据库中某些列的值在表内是唯一的，用来唯一标识记录。主要包括以下几种：

# 2.1 主键约束 (Primary Key Constraint)

主键 (Primary Key) 是数据库中用于唯一标识表中每一行的列（或列的组合）。主键的值不能重复，也不能为空 ( NULL )。

示例：

row

CREATE TABLE Student (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    age INTEGER
);

在此示例中， student_id 是主键，保证每个学生都有唯一的 student_id 。

# 2.2 唯一约束 (Unique Constraint)

唯一约束 (Unique) 确保列中的所有值都是唯一的，但允许 NULL 值。与主键不同，一个表可以有多个唯一约束。

示例：

row

CREATE TABLE Student (
    student_id INTEGER PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

此示例中， email 列上的唯一约束确保每个学生的电子邮件地址都是唯一的。

# 3. 外键约束 (Foreign Key Constraints)

外键约束 (Foreign Key) 用于确保两个表之间的参照完整性。它保证一个表中的值必须在另一个表中存在。

示例：

row

CREATE TABLE Student (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Enrollment (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

在此示例中， Enrollment 表中的 student_id 是一个外键，引用 Student 表中的 student_id 。这确保了每个注册记录中的 student_id 都必须存在于 Student 表中。

# 4. 检查约束 (Check Constraints)

检查约束 (Check) 用于指定列的值必须满足的条件。它们提供了一种强制执行复杂条件的机制。

示例：

row

CREATE TABLE Student (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    age INTEGER CHECK (age >= 18)
);

此示例中， age 列的值必须大于等于 18，确保插入的学生必须年满 18 岁。

# 5. 语义约束 (Semantic Integrity Constraints)

语义约束 (Semantic Constraints) 是对数据逻辑含义的约束，确保数据的有效性和一致性。它们通常涉及特定领域的业务逻辑规则。

示例：

row

CREATE TABLE Employee (
    emp_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    hire_date DATE,
    birth_date DATE,
    CHECK (hire_date > birth_date)
);

在这个例子中， hire_date 必须晚于 birth_date ，确保员工的入职日期必须在他们的出生日期之后。

# 6. 为已经创建好的表添加约束

row

-- 添加约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 类型 (列名);
-- 删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
-- 添加主键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列名);
-- 添加外键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (列名) REFERENCES 参考表 (参考列);
-- 添加唯一（unique)
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名);
-- 命名约束
ALTER TABLE Students ADD CONSTRAINT unique_email UNIQUE (email);
-- 删除已经命名的约束
ALTER TABLE Students DROP CONSTRAINT unique_email;

-- 举例
ALTER TABLE Lecture ADD CONSTRAINT ClassroomConflict 
UNIQUE(classroomId, semester, year, classTime);
-- 唯一的一个集合

# 动态完整性约束（Dynamic Integrity Constraints）

动态完整性约束的目标是在数据发生变化时确保其合法性。这类约束在数据库的插入、更新、删除等操作后执行，以确保数据的完整性。它们常用于以下场景：

维护复杂的业务规则
确保数据的有效性
在特定事件发生时，自动执行相应的操作

# 1. 触发器（Triggers）

触发器是数据库中的一种机制，它可以在某些事件发生时（例如插入、更新或删除数据时）自动执行定义好的操作。触发器用于实现动态完整性约束，是动态约束的典型实现方式。

# 1.1 触发器的组成部分：

事件（Event）：触发器是基于特定事件触发的，常见的事件有 INSERT 、 UPDATE 和 DELETE 操作。
时机（Timing）：触发器可以在事件发生之前（ BEFORE ）或之后（ AFTER ）执行。
操作（Action）：当触发器被触发时，执行的 SQL 语句或函数。
作用范围（Scope）：触发器可以设置为针对每一行（ FOR EACH ROW ）或针对整个语句（ FOR EACH STATEMENT ）。

# 1.2 触发器的基本语法（以 PostgreSQL 为例）：

row

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

# 1.3 触发器的示例：

以下示例演示了如何在员工表中使用触发器来检查员工的工资是否在合理范围内（大于 0 且小于 100,000）：

创建触发器函数：

row

CREATE OR REPLACE FUNCTION check_salary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '工资不能为负';
    ELSIF NEW.salary > 100000 THEN
        RAISE EXCEPTION '工资超出上限';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

触发器函数语法介绍：
函数介绍
CREATE OR REPLACE FUNCTION
- 这是 PostgreSQL 中用于创建或替换（更新）一个函数的语法。
- CREATE 表示创建一个新的函数。
- OR REPLACE 表示如果同名函数已经存在，那么替换它。这避免了先删除再重新创建函数的麻烦。
check_salary()
- 这是函数的名称和参数部分。在这个例子中，函数名是 check_salary ，括号中为空表示这个函数不接受任何参数。
RETURNS TRIGGER
- RETURNS 表示函数的返回类型。
- 在这个例子中，返回类型是 TRIGGER ，意味着这是一个触发器函数，必须返回触发器类型的数据。所有的触发器函数都必须返回 TRIGGER 类型，而不是其他类型（如 INTEGER 或 TEXT ）。
- 触发器函数的返回值通常是 NEW 或 OLD ，表示返回新插入 / 更新的数据行或旧删除 / 更新的数据行。
AS $$
- AS $$ 是用来定义函数体的部分。 $$ 是一个定界符（delimiter），表示函数体的开始和结束。定界符 $$ 让函数体内的代码可以包含单引号等特殊字符，而不需要对这些字符进行转义。
- 换句话说， $$ 包围的是函数的具体逻辑和代码。
语法介绍
NEW 和 OLD
- NEW ：在 INSERT 或 UPDATE 操作中， NEW 包含新插入或更新的数据行。
- OLD ：在 UPDATE 或 DELETE 操作中， OLD 包含被更新或删除的旧数据行。
TG_OP
- 表示触发器触发的操作类型，可以是 'INSERT' 、 'UPDATE' 、 'DELETE' 。
TG_WHEN
- 表示触发器是 BEFORE 还是 AFTER 。
TG_TABLE_NAME
- 表示触发器所属的表的名字。
TG_NARGS 和 TG_ARGV[]
- 用于访问在创建触发器时传递给触发器函数的参数。

创建触发器：
row
1
2
3
4
CREATE TRIGGER salary_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();
在这个例子中，每当试图插入或更新 employees 表时，触发器会调用 check_salary 函数，检查工资的合理性。如果工资不在合法范围内，会抛出异常并阻止该操作。

# 2. 触发器的用途：

触发器可以用于以下几种主要场景：

# 2.1 数据验证：

确保插入或更新的数据满足特定条件，例如值的范围或数据的一致性要求。触发器可以在数据变更之前检查数据是否符合约束条件，避免不合法的数据被插入或更新。

# 2.2 自动计算：

在插入或更新数据时，自动计算和更新相关字段的值。例如，可以创建一个触发器，在订单被更新时，自动重新计算总价。

# 2.3 业务规则的执行：

触发器可以帮助实施复杂的业务规则。例如，确保员工的工资不得高于其主管的工资。

# 2.4 维护数据完整性：

触发器可以用于自动更新或删除与某个表相关的其他表中的数据。例如，使用 ON DELETE CASCADE 可以在删除某条主记录时自动删除所有相关的子记录。

# 2.5 审计和日志记录：

触发器可以用于跟踪对关键数据的修改，记录修改操作的时间、执行人等信息。例如，每当某个用户数据被修改时，可以记录修改时间和修改内容。

# 3. 触发器的执行时机：

触发器可以配置为在以下几个时机触发：

BEFORE：在执行 INSERT 、 UPDATE 或 DELETE 语句之前触发。通常用于验证数据的有效性或在操作之前修改数据。
AFTER：在执行 INSERT 、 UPDATE 或 DELETE 语句之后触发。通常用于更新其他表、执行日志记录等。

# 4. 动态完整性约束的示例：

以下是一个动态完整性约束的具体例子，当在 employees 表中插入或更新员工记录时，触发器会检查新插入或更新的工资是否合法：

row

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_salary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '工资不能为负';
    ELSIF NEW.salary > 100000 THEN
        RAISE EXCEPTION '工资超出上限';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER salary_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();

在这个例子中， check_salary 函数定义了工资必须在合理范围内的约束条件。通过 salary_trigger 触发器，每当有新的员工数据被插入或员工数据被更新时，系统会自动检查工资的合法性。如果工资不合法，操作将被阻止。

# 5. 触发器的管理：

禁用触发器：在某些情况下（例如批量数据导入），可能需要临时禁用触发器以提高性能。可以使用以下命令禁用触发器：
row
1
ALTER TABLE employees DISABLE TRIGGER salary_trigger;
启用触发器：执行完批量数据导入后，可以重新启用触发器：
row
1
ALTER TABLE employees ENABLE TRIGGER salary_trigger
删除触发器：如果不再需要某个触发器，可以使用以下命令删除它：
row
1
ALTER TABLE employees ENABLE TRIGGER salary_trigger

# 总结：

动态完整性约束通过触发器机制来确保数据库在变化过程中的数据完整性和一致性。它们主要用于处理数据库状态改变时的约束条件，维护数据的合法性。触发器在数据库系统中具有强大的功能，能够在数据操作的过程中自动执行验证和维护操作，但使用时也需要谨慎，以避免性能问题或意外的副作用。

# 处理违规约束

# 1. 拒绝操作

定义：当数据库中的某个操作违反了完整性约束时，最常见的处理方式就是拒绝执行该操作。这种方式直接阻止违反约束的插入、更新或删除操作，从而确保数据的完整性。
场景：
- 插入一条记录时，如果它违反了唯一约束或主键约束，数据库将拒绝该插入操作。
- 更新某列数据时，如果更新后的值不符合 CHECK 约束条件（例如超过了规定的范围），则数据库会拒绝该更新操作。
示例：
row
1
INSERT INTO Student (student_id, age) VALUES (1, -5);
如果 age 列的 CHECK 约束是年龄必须大于 0，则插入负年龄的操作会被拒绝。

# 2. 回滚事务

定义：如果在一个事务中有任何操作违反了完整性约束，整个事务将会被回滚。回滚意味着撤销事务中的所有操作，使数据库返回到事务开始之前的状态。
场景：
- 一个事务中包含多个操作，如果其中任意一个操作违反了完整性约束，所有操作都将被撤销。这样做可以确保数据库不会因为部分执行成功而陷入不一致的状态。

示例：

row

BEGIN;

-- 插入一条记录
INSERT INTO Accounts (account_id, balance) VALUES (1, 100);

-- 尝试更新一条违反约束的记录
UPDATE Accounts SET balance = -200 WHERE account_id = 1;

-- 如果更新违反了 CHECK 约束，回滚整个事务
ROLLBACK;

在此示例中， balance 列可能有一个 CHECK 约束，要求余额不能为负数。如果 UPDATE 语句违反了该约束，整个事务会回滚， INSERT 操作也会被撤销。

# 3. 执行 “维护” 操作

定义：当约束被违反时，数据库可以自动执行某些维护操作，以恢复数据库到合法状态。这种方式常见于外键约束和级联操作（Cascading Actions）。
场景：
- 当删除父表中的记录时，如果该记录被子表引用，外键约束可能会被违反。在这种情况下，数据库可以通过级联删除或级联更新来维护数据一致性。
- 另一种维护操作是将相关外键字段设置为 NULL 或默认值，以避免参照完整性约束被违反。
维护操作类型：
- ON DELETE CASCADE：删除父表记录时，自动删除子表中所有引用该记录的行。
- ON UPDATE CASCADE：当父表中关键字段被更新时，自动更新子表中所有相关的外键字段。
- ON DELETE/UPDATE SET NULL：当父表记录被删除或更新时，子表的外键列被设置为 NULL 。
- ON DELETE/UPDATE SET DEFAULT：当父表记录被删除或更新时，子表的外键列被设置为默认值。

示例：

row

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    ON DELETE CASCADE
);

在这个示例中，如果 Customers 表中的某个客户记录被删除，则 Orders 表中所有引用该客户的订单记录也会自动被删除。

# 总结

拒绝操作：直接阻止违反约束的插入、更新或删除操作。
回滚事务：如果事务中的任何操作违反约束，整个事务都会被撤销。
执行维护操作：通过设置级联操作或将外键字段设置为 NULL 或默认值，自动维护数据库的完整性。