中级SQL：事务与完整性约束详解

### 中级 SQL：事务与完整性约束详解 #### 1. 事务处理 在数据库操作中，事务是一组不可分割的操作序列。将事务的概念应用到数据库操作中，更新语句应作为一个单一事务执行。若事务在执行其中一条语句时出错，会撤销该事务之前语句产生的影响，避免数据库处于部分更新状态。 如果程序在未执行提交或回滚命令时终止，更新操作可能会被提交或回滚，具体取决于数据库的实现，标准并未明确规定。 在许多 SQL 实现中，默认情况下每个 SQL 语句本身就是一个事务，执行后会立即提交。若要执行包含多个 SQL 语句的事务，必须关闭自动提交功能。关闭自动提交的方法因具体 SQL 实现而异，不过可以使用如 JDBC 或 ODBC 等应用程序接口来实现，这是一种标准的做法。 SQL:1999 标准提供了一种更好的选择（目前只有部分 SQL 实现支持），即使用 `begin atomic ... end` 关键字将多个 SQL 语句括起来，这些语句将构成一个单一事务。 #### 2. 完整性约束概述 完整性约束确保授权用户对数据库所做的更改不会导致数据一致性的丢失，从而防止数据库受到意外损坏。以下是一些完整性约束的示例： - 教师姓名不能为空。 - 没有两个教师可以有相同的教师 ID。 - 课程关系中的每个系名必须在系关系中有匹配的系名。 - 系的预算必须大于 0 美元。 一般来说，完整性约束可以是与数据库相关的任意谓词，但任意谓词的测试可能会很昂贵。因此，大多数数据库系统允许用户指定可以以最小开销进行测试的完整性约束。 完整性约束通常在数据库模式设计过程中确定，并作为 `create table` 命令的一部分进行声明。也可以使用 `alter table table-name add constraint` 命令将完整性约束添加到现有关系中。执行该命令时，系统会先确保关系满足指定的约束，如果满足则添加约束，否则拒绝该命令。 #### 3. 单关系约束 `create table` 命令除了可以定义表结构外，还可以包含完整性约束语句。除了主键约束外，还可以包含以下几种完整性约束： - `not null` - `unique` - `check(<predicate>)` ##### 3.1 Not Null 约束 在 SQL 中，默认情况下空值是所有域的成员，因此每个属性都可以取空值。但对于某些属性，空值可能不合适。例如，学生关系中姓名为空的元组没有提供有用信息，系预算也不应该为空。可以通过以下方式声明属性不允许为空： ```sql name varchar(20) not null budget numeric(12,2) not null ``` `not null` 规范禁止向声明为非空的属性插入空值，任何试图插入空值的数据库修改操作都会产生错误诊断信息。 需要注意的是，SQL 禁止关系模式的主键包含空值。例如，在大学数据库的系关系中，如果 `dept name` 被声明为主键，它就不能取空值，因此不需要显式声明为 `not null`。 ##### 3.2 Unique 约束 SQL 支持 `unique (Aj1, Aj2, ..., Ajm)` 完整性约束，该规范表示属性 `Aj1, Aj2, ..., Ajm` 构成候选键，即关系中没有两个元组在所有列出的属性上的值都相等。 候选键属性允许为空，除非它们被显式声明为 `not null`。需要注意的是，空值不等于任何其他值。 ##### 3.3 check 子句 在关系声明中使用 `check(P)` 子句可以指定一个谓词 `P`，关系中的每个元组都必须满足该谓词。 `check` 子句的常见用途是确保属性值满足指定条件，从而创建一个强大的类型系统。例如，在创建系关系的 `create table` 命令中使用 `check (budget > 0)` 子句可以确保预算值为非负。 以下是另一个示例： ```sql create table section ( course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), building varchar(15), room_number varchar(7), time_slot_id varchar(4), primary key (course_id, sec_id, semester, year), check (semester in ('Fall', 'Winter', 'Spring', 'Summer')) ); ``` 这里使用 `check` 子句模拟了一个枚举类型，要求 `semester` 属性的值必须是 `'Fall'`、`'Winter'`、`'Spring'` 或 `'Summer'` 之一。 根据 SQL 标准，`check` 子句中的谓词可以是包含子查询的任意谓词，但目前广泛使用的数据库产品都不允许谓词中包含子查询。 #### 4. 引用完整性 引用完整性要求一个关系中某个属性集的值也必须出现在另一个关系的某个属性集中。可以使用 `foreign key` 子句在 SQL 的 `create table` 语句中指定外键。 例如，在大学数据库的课程表定义中，`foreign key (dept name) references department` 声明指定了每个课程元组中的系名必须存在于系关系中，否则可能会出现课程指定不存在的系名的情况。 更一般地，设 `r1` 和 `r2` 是两个关系，其属性集分别为 `R1` 和 `R2`，主键分别为 `K1` 和 `K2`。如果对于 `r2` 中的每个元组 `t2`，都必须存在 `r1` 中的一个元组 `t1`，使得 `t1.K1 = t2.`，则称 `R2` 的一个子集 `` 是引用 `r1` 中 `K1` 的外键。这种要求被称为引用完整性约束或子集依赖。 默认情况下，SQL 中的外键引用被引用表的主键属性。SQL 也支持显式指定被引用关系的属性列表，但这些属性必须被声明为被引用关系的候选键，可以使用主键约束或唯一约束来实现。更一般的引用完整性