数据库复习——数据库的完整性约束（静态约束与动态约束示例）

前言

总结一下，数据库完整性约束几个列子

正文

一、静态完整性：Create Table

CreateTable有三种功能：定义关系模式、定义完整性约束和定义物理存储特性
定义完整性约束条件：

• 列完整性
• 表完整性

Col_constr列约束

• 一种域约束类型，对单一列的值进行约束
  Col_constr列约束：只能应用在单一列上，其后面的约束如UNIQUE,PRIMARY KEY及search_cond只能是单一列唯一、单一列为主键、和单一列相关

示例

Create Table Student ( 
S# char(8) not null unique, 
Sname char(10),
Ssex char(2) constraint ctssex check (Ssex=‘男’ or
Ssex=‘女’), Sage integer check (Sage>=1 and Sage<150),
D# char(2) references Dept(D#) on delete cascade,
Sclass char(6) );
//假定Ssex只能取{男，女}, 1=<Sage<=150, D#是外键

示例

Create Table Course ( 
C# char(3) , 
Cname char(12), 
Chours integer,
Credit float(1) constraint ctcredit check (Credit >=0.0 and
Credit<=5.0 ), 
T# char(3) references Teacher(T#) on delete cascade );
//假定每门课学分最多5分，最少0分

table_constr表约束

• 一种关系约束类型，对多列或元组的值进行约束
  table_constr表约束：是应用在关系上，即对关系的多列或元组进行约束，列约束是其特例

示例

Create Table Student ( S# char(8) not null unique, Sname char(10),
Ssex char(2) constraint ctssex check (Ssex=‘男’ or
Ssex=‘女’), Sage integer check (Sage>1 and Sage<150),
D# char(2) references Dept(D#) on delete cascade,
Sclass char(6) , 
primary key(S#) );

Create Table Course ( C# char(3) , Cname char(12), Chours integer,
Credit float(1) constraint ctcredit check (Credit >=0.0 and
Credit<=5.0 ), T# char(3) references Teacher(T#) on delete cascade, 
primary key(C#),
constraint ctcc check(Chours/Credit = 20) );
//假定严格约束20学时一个学分

示例

Create Table SC ( S# char(8), C# char(3),
Score float(1) constraint ctscore check (Score>=0.0 and
Score<=100.0),
forergn key (S#) references student(S#) on delete cascade,
forergn key (C#) references course(C#) on delete cascade );

check中的条件可以是Select-From-Where内任何Where后的语句，包含子查询。

撤消或追加约束

Create Table中定义的表约束或列约束可以在以后根据需要进行撤消或追加。撤消或追加约束的语句是 Alter Table(不同系统可能有差异)
示例：撤消SC表的ctscore约束(由此可见，未命名的约束是不能撤消)

Alter Table SC
DROP CONSTRAINT ctscore;

示例：若要再对SC表的score进行约束，比如分数在0~150之间，则可新增
加一个约束。在Oracle中增加新约束，需要通过修改列的定义来完成

Alter Table SC
Modify ( Score float(1) constraint nctscore check (Score>=0.0 and
Score<=150.0) );

有些DBMS支持独立的追加约束,注意书写格式可能有些差异
示例：

Alter Table SC
Add Constraint nctscore check (Score>=0.0 and Score<=150.0) );

二、静态完整性：断言ASSERTION

一个断言就是一个谓词表达式，它表达了希望数据库总能满足的条件

• 表约束和列约束就是一些特殊的断言
• SQL还提供了复杂条件表达的断言。其语法形式为：

CREATE ASSERTION < assertion-name > CHECK < predicate >

• 当一个断言创建后，系统将检测其有效性，并在每一次更新中测试更新是否违反该断言。任何断言不为真的值都将被拒绝执行

同是静态约束的 断言ASSERTION和Create Table，区别在于，ASSERTION能实现多个表或聚集操作复杂的完整性约束，Create Table实现的单个表的操作

示例
“每个分行的贷款总量必须小于该分行所有账户的余额总和”

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 (balance ) from account
       where account.branch_name =
             branch.branch_name )))

account(branch_name, account_number,…, balance) //分行，账户及其余额
loan(branch_name , loan_number, amount,) //分行的每一笔贷款
branch(branch_name, … ) //分行

三、动态完整性：触发器

Trigger是一种过程完整性约束(相比之下，Create Table中定义的都是非过程性约束),是一段程序，该程序可以在特定的时刻被自动触发执行，比如在一次更新操作之前执行，或在更新操作之后执行。

• 触发器Trigger意义：当某一事件发生时( Before | After ),对该事件产生的结果(或是每一元组，或是整个操作的所有元组), 检查条件 search_condition ,如果满足条件，则执行后面的程序段。条件或程序段中引用的变量可用 corr_name_def 来限定。

• 事件：BEFORE | AFTER { INSERT | DELETE | UPDATE …}

  • 当一个事件(Insert, Delete, 或Update)发生之前Before或发生之后After触发
  • 操作发生，执行触发器操作需处理两组值：更新前的值和更新后的值，这两个值由corr_name_def的使用来区分

• corr_name_def的定义

{ OLD [ROW] [AS] old_row_corr_name //更新前的旧元组命别名为
| NEW [ROW] [AS] new_row_corr_name //更新后的新元组命别名为
| OLD TABLE [AS] old_table_corr_name //更新前的旧Table命别名为
| NEW TABLE [AS] new_table_corr_name //更新后的新Table命别名为
}

• corr_name_def将在检测条件或后面的动作程序段中被引用处理

具体示例

示例一
设计一个触发器当进行Teacher表更新元组时, 使其工资只能升不能降

示例二
假设student(S#, Sname, SumCourse), SumCourse为该同学已学习课程的门数，初始值为0，以后每选修一门都要对其增1 。设计一个触发器自动完成上述功能。

示例三
假设student(S#, Sname, Sage, Ssex, Sclass)中某一学生要变更其主码S#的值，如使其原来的98030101变更为99030131, 此时sc表中该同学已选课记录的S#也需自动随其改变。设计一个触发器完成上述功能

示例四
假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课也都要删除。设计一个触发器完成上述功能

示例五
假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课中的S#都要置为空值。设计一个触发器完成上述功能

示例六
假设Dept(D#, Dname, Dean), 而Dean一定是该系教师Teacher(T#, Tname, D#, Salary)中工资最高的教师。设计一个触发器完成上述功能

create trigger teacher_chgsal before update of salary
on teacher
referencing new x, old y
for each row when (x.salary < y.salary)
begin
raise_application_error(-20003, 'invalid salary on update');
//此条语句为Oracle的错误处理函数
end;

create trigger sumc after insert on sc
referencing new row newi
for each row
begin
update student set SumCourse = SumCourse + 1
where S# = :newi.S# ;
end;

create trigger updS# after update of S# on student
referencing old oldi, new newi
for each row
begin
update sc set S# = newi.S# where S# = :oldi.S# ;
end;

create trigger delS# after delete on Student
referencing old oldi
for each row
begin
delete from sc where S# = :oldi.S# ;
end;

create trigger delS# after delete on Student
referencing old oldi
for each row
begin
update sc set S# = Null where S# = :oldi.S# ;
end;

create trigger upddean before update of Dean on Dept
referencing old oldi, new newi
for each row when ( dean not in
(select Tname from Teacher where D# = :newi.D#
and salary >=
all (select salary from Teacher where D# = :newi.D#))
begin
raise_application_error(-20003, 'invalid Dean on update');
end;