一、SQL Data Types and Schemas

1. 用户定义数据类型 User-defined types

   • 用户定义数据类型允许创建枚举、结构体等复杂数据类型
   • 在使用上，与 SQL 的默认数据类型无差异

   -- 创建自定义数据类型
   Create type person_name as varchar (20)
   -- 使用自定义数据类型
   Create table student
   		               (sno char(10) primary key, 
      		              sname person_name
      		             )
   -- 删除自定义数据类型
   Drop type person_name 
   

2. 域 Domains

   • 域是对已有数据类型的约束封装，它不能创建全新的数据结构，而是基于现有数据类型定义一套通用的规则
   • 适用于多个表需要复用相同的数据约束的场景
   • 允许为域添加默认值和约束条件（ CHECK）

   -- 创建域
   Create domain Dollars as numeric(12, 2) not null
   -- 使用域
   Create table employee 
   			        (eno char(10) primary key, 
   			         salary Dollars
   			        ); 
   

3. 大型对象类型 Large Object

   • 大型对象使用 Large Object 类型存储，分为以下两类：
     • blob（binary large object）：存储二进制对象
     • clob（character large object）：存储字符对象
   • 程序请求大型对象时，返回指向大型对象的指针，而非返回对象本体

   Create table students 
   						(sid char(10) primary key, 
   						 name varchar(10), 
   						 photo blob(20MB), 
   						 cv clob(10KB)) ;
   

二、完整性约束 Integrity Constraints

1. 表和域的完整性约束

1. 单个表的约束

   • Not NULL
   • Primary Key
   • Unique
   • Check(P) （P 为检查条件）

2. 域的约束

   -- 创建带约束的域
   -- 其中子句 Constraint ... 是可选的，用于当约束违背时指示更新违反了哪个约束
   Create domain Dollars as numeric(12, 2) not null
   Constraint money-positive-test Check (Dollars >= 0.00); 
   

2. 参照完整性 Referential Integrity

1. 基本概念

   • 在参照关系中，外键或者为 NULL，或者其值在被参照关系中实际存在
   • 在元组被插入、更新、删除时，都会对参照完整性进行检查
   • 仅在事务结束时检查引用完整性；即：如果事务的步骤中产生了冲突，但后续步骤在事务结束前删除了冲突，则该事务可以正常执行

2. 级联 Cascading

   • 级联更新与删除（Cascading Update / Deletion）
     • 当被参照的元组发生更新或删除，导致参照关系失效时，参照它的元组将同步进行更新或删除
     • 如果跨多个关系存在外键依赖关系链，并且为每个依赖关系指定了级联删除，则链一端的删除或更新可以传播到整个链中
     • 如果级联更新或删除导致进一步的级联作无法处理的约束冲突，则系统会中止事务，由事务及其级联作引起的所有更改都将被撤消
   • 级联操作（Cascading Actions）
     • 当被参照的元组发生更新或删除，导致参照关系失效时，参照它的元组将把对应的外键置为 NULL

3. SQL 实现

   • 指定主键、候选键

     -- 指定主键
     primary key (customer-name, account-number)
     -- 指定候选键
     unique key (...)
     

   • 指定外键

     -- 默认情况，外键引用被引用表的主键属性
     foreign key (account-number) references account
     -- 指定外键引用的属性名称
     foreign key (account-number) references account(account-number)
     -- 行内简写
     account-number char(10) references account
     

   • 指定级联

     -- 中括号内为可选项，分别对应级联删除、级联更新、级联操作
     -- 在 SQL 语句中，不要出现下面的中括号
     foreign key (branch-name) references branch 
     		[ on delete cascade] 
     		[ on update cascade] 
     		[ on delete set null]
     		[ on update set null]
     		[ on delete set default]
     		[ on update set default]
     

   示例：

   Create table depositor 
   	(customer-name	char(20), 
   	 account-number	char(10), 
   	 primary key (customer-name, account-number), 
   	 foreign key (account-number) references account on delete cascade, 
   	 foreign key (customer-name) references customer on delete cascade
   	 ); 
   

3. 断言 Assertions

1. 基本概念

   • 断言是用于多个关系的复杂检查条件，是我们希望数据库始终满足的条件
   • 系统将在每次可能违反的断言的更新中测试断言的有效性，可能会带来大量开销

2. SQL 实现

   • 基本语法

     Create assertion assertion-name
     		Check <condition>;
     

   • SQL 无法表示形如 $for\ all\ X,P(X)$ 的表达式，需要转化为 $not\ exist\ X,such\ that\ not\ P(X)$ 的形式

   **示例：**设置断言 “对每一笔借款, 至少有一个借款人有存款 $1000以上”

   **分析：**上述断言等同于 “不存在这样一笔借款，其不存在存款在 $1000以上的借款人”

   

4. 触发器 Triggers

1. 基本概念

   • 触发器是系统自动执行的语句，作为对数据库的修改的副作用
   • 要设计触发机制，必须指定触发条件，以及触发器执行时要执行的操作
   • 分为 行级触发器 和 语句级触发器
   • 触发器早些年用于维护摘要数据等（例如：每个部门的总工资），但如今有更好的替代方案
   • 有时需要在数据库更新时触发外部操作（例如：库存不足时打开警报灯）；但触发器无法直接执行外部操作，实现方案如下：
     • 触发器将要执行的操作记录在一个单独的表中
     • 有一个外部进程，该进程重复扫描表、执行外部操作并从表中删除记录

   示例：

   假设银行不允许账户余额为负数，而是通过以下方式处理透支：

   • 将账户余额设置为零
   • 创建透支金额的贷款，为该贷款提供与透支账户账号相同的贷款编号

   于是执行触发器的条件是：导致负余额值的账户关系更新

2. 行级触发器 Row-Level Trigger

   • 关键词： FOR EACH ROW
   • 触发时机：BEFORE 或 AFTER ，针对每一行数据触发一次
   • 触发操作：INSERT、UPDATE、DELETE
   • 支持 NEW 和 OLD 关键字，可访问受影响的具体行数据
     • NEW 关键字不适于 DELETE
     • OLD 关键字不适于 INSERT
   • 可以使用 Referencing Old Row AS ... 或 Referencing New Row AS ... 语句来引用受影响行

   -- 中括号内为可选项，用于为触发器指定具体的触发属性列
   -- 在 SQL 语句中，不要出现下面的中括号
   CREATE TRIGGER trg-name
   BEFORE UPDATE [OF attribute-name] ON table-name
   FOR EACH ROW
   BEGIN
       IF NEW.salary < 0 THEN
           ... ;
       END IF;
   END;
   

   -- 中括号内为可选项，用于为触发器指定具体的触发属性列
   -- 在 SQL 语句中，不要出现下面的中括号
   CREATE TRIGGER trg-name
   BEFORE UPDATE [OF attribute-name] ON table-name
   REFERENCING OLD ROW AS orow, NEW ROW AS nrow
   FOR EACH ROW
   BEGIN
       IF nrow.salary < 0 THEN
           ... ;
       END IF;
   END;
   

3. 语句级触发器 Statement-Level Trigger

   • 关键词： FOR EACH STATEMENT
   • 每个 SQL 语句执行时只触发一次（无论影响多少行）
   • 不支持 NEW 和 OLD，因为它不针对具体行
   • 可以使用 Referencing Old Table AS ... 或 Referencing New Table AS ... 来引用包含受影响行的临时表 （称为转换表，Transition Tables）

   CREATE TRIGGER trg-name
   AFTER DELETE ON table-name
   FOR EACH STATEMENT
   BEGIN
       ... ;
   END;