结构化查询语言之 SQL 完整性约束

  完整性约束(Integrity Constraint)保证授权用户对数据库所做的修改不会破坏数据的一致性，故而完整性约束防止的是对数据的意外破坏。
​  完整性约束通常被看成是数据库模式设计过程的一部分，它作为用于创建关系的create table命令的一部分被声明，而可以通过使用alter table table-name add constraint命令施加到已有关系上，其中constraint可以是关系上的任意约束(执行上述命令时，系统首先保证关系满足指定的约束，满足则施加到关系上，不满足则拒绝执行)。

1. 单个关系上的约束

• not null

  • 空值是所有域的成员，在默认情况下是SQL中每个属性的合法值，然而空值对一些属性(域)来说并不合适；
  • not null 声明禁止在该属性上插入空值；
  • 主键不必显示的声明为not null;

• unique

  • unique(A_1, A_2, …, A_m)；
  • unique声明指出属性A_1, A_2, …, A_m形成一个候选码，即在关系中没有两个元祖能在列出的属性上取值相同 (候选码属性可为null，除非被显式声明为not null)；

• check(<谓词>)

  • check(P)子句指定一个谓词P，关系中每个元祖都必须满足谓词P；

  • 通常用check子句来保证属性值满足指定的条件，实际上创建了一个强大的类型系统；

  • 根据SQL标准，check子句的谓词可以是包括子查询在内的任意谓词；

2. 参照完整性约束(子集依赖)

• 定义：保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现的情况；

• 更一般地：

  令关系r_1, r_2的属性集分别为R_1和R_2, 主码分别为K_1和K_2, 如果要求对r_2中任意的元祖t_2, 均存在r_1中的元祖t_1使得$t_1.K_1=t_2.a$, 称R_2的子集a为参照关系r_1中K_1的外码(为使参照完整性约束有意义，a和K_1必须是相容的属性集)；

• references子句：显示指定被参照关系的属性列表(必须为被参照关系的候选码：unique或primary key约束) 的references子句

• create table course
  (...
  foreign key (dept_name) references department(dept_name) 
  	on delete cascade
  	on update cascade
  ...);
  

• 注：

  • 由于有了与外码声明相关联的on delete casade，如果删除department中的元祖而导致违反了参照完整性约束，删除虽不被系统拒绝，但是会对course关系作“级联”删除，即删除参照了被删除关系的元祖；
  • 由于有了与外码声明相关联的on update casade，如果更新被参照字段违反了约束，则更新也不被拒绝，而是将course中参照的元祖的dept_name字段也改为新值。

---

例子(1&2)：

create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department(dept_name)
		on delete set null
	);

3. 断言(Assertion)：Mysql不支持

• 一个断言就是一个谓词，它表达了我们希望数据库总能满足的一个条件；
• 域约束和参照完整性约束是断言的特殊形式；
• 断言创建形式
  • create assertion <assertion-name> check <predicate>
• 当创建断言时，系统要检测其有效性
  • 若断言有效，则以后只有不破坏断言的数据库修改才被允许；
  • 如果断言较复杂，则坚持会带来相当大的开销；
• 例子
  • 对于student关系中的每个元祖，它在属性tot_cred上的取值必须等于该生所成功修完课程的学分总和
    • 由于SQL不提供“for all X, P(X)”结构，故通过等价的“not exists X such that not P(X)”实现

create assertion credits_earned_constraint check
(not exists 
(select ID
 from student
 where tot_cred<>(select sum(credits)from takes natural join course))
)

  可见，Mysql并不支持