Integrity Constraints (ICs) 完整性约束

完整性约束是数据库中的一种机制，用于确保数据的准确性和一致性。这些约束可以在创建表或之后的任何时间定义。主要有以下几种类型的完整性约束：

主键约束（Primary Key Constraint）：确保列（或列组合）具有唯一的值，并且每个表中的每一行都有一个主键值。
唯一约束（Unique Constraint）：确保列（或列组合）的所有值都是唯一的。
外键约束（Foreign Key Constraint）：确保引用另一个表的数据的完整性。
非空约束（Not Null Constraint）：确保列不能有 NULL 值。
检查约束（Check Constraint）：确保列中的值满足指定的条件。

优点

数据一致性：完整性约束确保了数据库中的数据满足预定义的规则，从而保证了数据的一致性。
数据准确性：通过防止无效数据输入到数据库，完整性约束提高了数据的准确性。
避免冗余：主键和唯一约束避免了数据的冗余，提高了数据的效率。
引用完整性：外键约束保证了在相关表之间的数据的引用完整性。
业务规则实施：检查约束允许数据库在表级别实施业务规则。

Static Integrity Constraints 静态完整性约束

Static Integrity Constraints，也被称为静态完整性约束，是数据库中的一种约束，它要求数据库中的所有数据在任何时候都必须满足某些特定的条件。这些约束是在数据库定义时指定的，并且在数据库的整个生命周期中都保持不变。

静态完整性约束：

键约束（Key Constraints）：确保某一列（或列的组合）在表中具有唯一值，每个表中的每一行都有一个主键值。
外键约束（Foreign Key Constraints）是数据库中的一种约束，它用于建立两个表之间的链接。外键是一个表中的字段，它的值匹配另一个表的主键。
例如，如果你有一个订单表和一个客户表，订单表中可能有一个客户 ID 字段，这个字段是客户表的主键的外键。外键约束会确保每个订单都对应一个真实存在的客户。
域约束（Domain Constraints）：这种约束定义了一个字段可以包含的值的范围。
例如，一个年龄字段可能被限制为只能包含 0 到 100 之间的整数。
域检查约束（Domain Check Constraints）是数据库中的一种约束，它定义了一个字段可以接受的值的范围或类型。
例如，如果你有一个年龄字段，你可能会设置一个域检查约束，以确保年龄字段只能包含 0 到 100 之间的整数。如果试图插入一个负数或超过 100 的值，数据库将拒绝这个操作并返回一个错误。（不是我就不能活到 100 岁吗？）
参照完整性约束（Referential Integrity Constraints）：这种约束要求在一个表中引用另一个表的数据时，被引用的数据必须真实存在。
例如，如果一个订单表中有一个字段引用了客户表中的客户 ID，那么每个订单中的客户 ID 必须在客户表中有对应的记录。
语义完整性约束（Semantic Integrity Constraints）：这是一种更高级的约束，它涉及到数据的含义和业务规则。例如：
- 一个规则可能是“员工的工资不能超过其经理的工资”。
- 学生的年龄不能小于 5 岁，也不能大于 30 岁。这是一个关于学生年龄的业务规则。
- 一个订单的总价不能小于其中所有商品价格的总和。这是一个关于订单和商品价格的业务规则。
- 一个员工的入职日期不能晚于他的离职日期。这是一个关于员工入职和离职日期的业务规则。
完整性约束命名（Naming Integrity Constraints）是数据库设计中的一个重要步骤，它可以帮助你更好地理解和管理你的数据库约束。约束的命名应该清晰、简洁，并且能够准确地描述约束的作用。
例如，如果你有一个约束是确保员工的工资不能超过其经理的工资，你可以将这个约束命名为"EmployeeSalaryLessThanManager"。
断言（Assertions）：这是一种特殊类型的约束，它在整个数据库而不仅仅是单个表上定义条件。
例如，一个断言可能是“所有部门的总预算必须小于公司的总预算”

延迟约束检查（Deferring Constraint Checking）

延迟约束检查（Deferring Constraint Checking）可以分为两种类型：不可延迟（Immediate）和可延迟（Deferrable）。

不可延迟（Immediate）：这是默认的类型，数据库在每次数据变更（如 INSERT、UPDATE 或 DELETE）后立即检查约束。如果约束条件不满足，数据库将回滚变更并返回错误。
可延迟（Deferrable）：在这种类型下，数据库将约束检查推迟到事务结束时进行。这在某些情况下是非常有用的，例如，当你需要在同一个事务中更新两个相互引用的表时。

Dynamic Integrity Constraints 动态完整性约束

动态完整性约束（Dynamic Integrity Constraints）是数据库中的一种约束，它主要关注数据库状态的变化。这种约束通常用于确保数据库事务不会导致违反预定义规则的数据库状态。

动态完整性约束通常通过触发器（Triggers）或存储过程（Stored Procedures）来实现。例如，您可能有一个规则，即“库存数量不能小于 0”。当有一个事务试图将库存数量减少到 0 以下时，动态完整性约束会阻止这个事务的执行。

触发器（Trigger）

触发器（Trigger）通常由三部分组成：事件（Event）、前置条件（Precondition）和动作（Action）。

事件（Event）：这是触发器被激活的数据库操作，例如 INSERT、UPDATE 或 DELETE。
前置条件（Precondition）：这是一个可选的测试，用于确定是否应执行触发器。例如，你可能只想在更新特定字段时才触发触发器。
动作（Action）：这是当触发器被激活时要执行的操作。这可以是一系列的 SQL 语句，例如，更新其他表，或者插入日志记录等。

创建触发器（Create Trigger）通常需要以下几个步骤：

使用 CREATE TRIGGER 语句开始。

指定触发器的名称。

指定触发器的触发事件，可以是 INSERT、UPDATE 或 DELETE。

指定触发器的触发时间，可以是 BEFORE 或 AFTER。

在 BEGIN 和 END 之间编写触发器的动作，这可以是一系列的 SQL 语句。

以下是一个创建触发器的示例：

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
END;

在创建触发器时，你可以选择触发器是在每行数据变更时触发（FOR EACH ROW），还是在每个 SQL 语句执行时触发（FOR EACH STATEMENT）。

FOR EACH ROW：如果 INSERT、UPDATE 或 DELETE 影响多行数据，触发器会为每行数据执行一次。
FOR EACH STATEMENT：无论 INSERT、UPDATE 或 DELETE 影响多少行数据，触发器只会执行一次。

此外，你还可以使用 OLD 和 NEW 关键字来引用被修改的数据：

OLD：引用被删除或更新前的旧数据。
NEW：引用将要插入或更新的新数据。

Transaction 事务

事务是数据库处理的基本单位，它是一系列操作，Begin 开始 Commit 结尾，这些操作要么全部执行，要么全部不执行。事务具有以下四个重要的属性，通常被称为 ACID 属性：

原子性（Atomicity）：事务是一个不可分割的工作单位，事务中包含的操作要么全部完成，要么全部不做。
一致性（Consistency）：事务必须使数据库从一个一致性状态转变为另一个一致性状态。
隔离性（Isolation）：事务的执行不会被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）：一旦事务提交，则其结果就是永久的，即使系统出现故障也不会丢失。

事务回滚（Transaction Rollback）

在数据库中，ROLLBACK 是一个重要的命令，它用于撤销未提交的事务。如果在事务中的某个点发生错误，或者你决定不再需要事务中的更改，你可以使用 ROLLBACK 命令来撤销事务。

在 Python 中，你可以使用 sqlite3 库（或其他数据库库，如 psycopg2 对于 PostgreSQL，pymysql 对于 MySQL 等）来执行 ROLLBACK 操作。以下是一个基本的示例：

import psycopg2

try:
  # 连接到数据库
  conn = psycopg2.connect('my_database.db')
  curs = conn.cursor()

  # 执行一些SQL操作
  curs.execute("INSERT INTO my_table VALUES (?, ?)", (value1, value2))

  # 提交事务
  conn.commit()

except Exception as e:
  # 如果在事务中发生错误，回滚事务
  print("An error occurred: ", e)
  conn.rollback()

finally:
  # 关闭数据库连接
  curs.close()

这段代码是 Python 操作数据库的一部分，主要包含了错误处理和数据库连接的清理工作。

conn.commit()：这是提交事务的操作。在数据库中，事务是一种保证数据一致性的机制。当你对数据库进行一系列操作（如 INSERT、UPDATE 或 DELETE）时，你可以将这些操作放在一个事务中，然后一起提交。如果在事务中的任何操作失败，整个事务都会被回滚，数据库将恢复到事务开始之前的状态。
except:：这是 Python 的错误处理机制。如果在try块中的代码执行过程中发生任何错误，控制流将立即跳转到except块。
conn.rollback()：这是回滚事务的操作。如果在事务中发生错误，你可以使用rollback方法撤销事务，数据库将恢复到事务开始之前的状态。

并发事务和串行化（Serializability）

在数据库中，当多个事务同时访问和修改数据时，就会发生并发事务。为了保证数据的一致性和完整性，数据库系统需要对并发事务进行管理。

串行化（Serializability）是一种并发控制的方法，它要求并发事务的执行结果必须与某种串行执行的结果相同。换句话说，无论事务是并发执行还是串行执行，最终的数据库状态都应该是一样的。

假设我们有两个事务 T1 和 T2，它们都要操作同一份数据。

事务 T1：读取一个值，增加 100，然后写回。
事务 T2：读取同一个值，减少 50，然后写回。
如果这两个事务串行执行，即先执行 T1，然后执行 T2，那么最终的结果将是原始值增加 50。

但是，如果这两个事务并发执行，可能会出现以下情况：

T1 读取原始值。
T2 读取原始值。
T1 增加 100，写回新值。
T2 减少 50，写回新值。
在这种情况下，T1 的修改被 T2 覆盖，最终的结果是原始值减少 50，这与串行执行的结果不一致。

为了避免这种情况，我们需要使用串行化（Serializability）来保证并发事务的正确执行。在串行化的环境中，数据库系统会使用锁或其他并发控制机制来确保事务的执行结果与串行执行的结果一致。

可串行调度（Serializable schedule）

可串行调度（Serializable schedule）是数据库并发控制的一种策略，它要求并发事务的执行结果必须与某种串行执行的结果相同。换句话说，无论事务是并发执行还是串行执行，最终的数据库状态都应该是一样的。

在可串行调度中，数据库系统会使用各种并发控制机制（如两阶段锁定协议）来确保事务的执行结果与串行执行的结果一致。这样可以避免并发事务带来的各种问题，如丢失更新、脏读、不可重复读和幻读。

冲突可串行化（Conflict Serializability）

冲突可串行化（Conflict Serializability）是一种并发控制的策略，它要求并发事务的冲突操作（即对同一数据项的读写操作）的顺序必须与某种串行执行的顺序相同。

例子：我们有两个事务 T1 和 T2，它们分别操作两个不同的表 X 和 Y。

事务 T1：读取表 X 的一个值（R1(X)），然后修改这个值（W1(X)）。
事务 T2：读取表 Y 的一个值（R2(Y)），然后修改这个值（W2(Y)）。

如果这两个事务串行执行，即先执行 T1，然后执行 T2，那么执行顺序将是：R1(X), W1(X), R2(Y), W2(Y)。

但是，因为 T1 和 T2 操作的是不同的表，它们之间没有冲突操作。
这意味着，无论是并发执行还是串行执行，最终的结果都是一样的。这就是冲突可串行化的一个例子。

例子，我们有两个事务 T1 和 T2，它们都要操作同一份数据 X 和 Y。

事务 T1：读取 X 和 Y 的值（R1(X), R1(Y)），然后修改这些值（W1(X), W1(Y)）。
事务 T2：读取 X 和 Y 的值（R2(X), R2(Y)），然后修改这些值（W2(X), W2(Y)）。

如果这两个事务串行执行，即先执行 T1，然后执行 T2，
那么执行顺序将是：R1(X), R1(Y), W1(X), W1(Y), R2(X), R2(Y), W2(X), W2(Y)。

但是，如果我们尝试将这两个事务并发执行，可能会出现以下情况：

T1 读取 X 的值（R1(X)）。
T1 读取 Y 的值（R1(Y)）。
T2 读取 X 的值（R2(X)）。
T2 读取 Y 的值（R2(Y)）。
T1 修改 Y 的值（W1(Y)）。
T2 修改 X 的值（W2(X)）。
T2 修改 Y 的值（W2(Y)）。
T1 修改 X 的值（W1(X)）。

在这种情况下，我们可以看到，R2(X)与 W1(X)冲突，R2(Y)与 W1(Y)冲突，W2(X)与 W1(X)冲突，W1(Y)与 W1(X)冲突，因此这个调度是非冲突可串行化的。但是，R1(X)与 R2(X)不冲突，因为它们都是读操作。