2023年7月7日发(作者：)

SQL⼊门（3）：定义约束断⾔assertion触发器trigger本⽂介绍数据库的完整性完整性控制程序: 指定规则,检查规则 (规则就是约束条件)动态约束 intergrity constraint::=(O,P,A,R)O : 数据集合, 约束的对象 ?: 列, 多列的元组集合P: 谓词条件: 什么样的约束?A: 触发条件: 什么时候检查?R: 响应动作: 不满⾜怎么办?按照约束对象分类:(1)域完整性约束条件: 施加在某⼀列上, ⽐如sage<25 and sage<40(2)关系完整性约束条件: 施加在表上, 涉及多列, 2<=hours/credit<=16按照约束来源分类:(1)结构约束: 主键约束, 外键约束,是否允许空值等 primary key, foreign key, not null(2) 内容约束: 取值范围, check(sage<25 and sage<40)按照约束状态分类:(1) 静态约束: 要求DB在任何时候都要满⾜的约束(2) 动态动态: DB改变状态时要满⾜的约束, 例如salary 只能加不能减, 不能由1000改为500.---> 触发器SQL⽀持如下⼏种约束:

静态约束中的列完整性 与表完整性, 动态约束中的触发器(⼀) 静态约束实现: create table(1) col_constr 列约束 (⼀种域约束类型, 对单⼀列的值进⾏约束)not null 列值⾮空primary key 主键not null + unique 就是⾮空+唯⼀性 ,实际上就是⼀个主键check (search_condition) 列值满⾜的条件,

references tablename(colname) , colname 是tablename 的主键on delete[ cascade| set null], 则删除被引⽤表的某⼀列v值时, 要将本表该列值为v 的记录删除 或者 列值更新为null, 缺省为⽆操作.

例: 创建⼀个表 studentcreate table student (sno char(8) not null unique, sname char(10),

--not null unique 表⽰主键ssex char(2) constraint ctssex check(ssex='男' or ssex='⼥'),

-- ctssex 是约束constraint 的名字. 之后可以单独处理sage integer check(sage>=1 and sage<150), -- 没有定义约束名, 则之后不能单独处理dno char(2) references dept(dno) on delete cascade, -- dno 在dept表中 是主键,

sclass char(6));on delete cascade 表⽰如果dept表中的某个'01'系被删除了,那么在student 表中该系所有学⽣的dept 值为null, 如果没有加这个, 那么dept表中的删除操作对student表没有影响.create table course (cno char(3), cname char(10), chours integer,credit float(1) constraint ctcredit check(credit>=1.0 and credit <=6.0),tno char(3) references teacher(tno) on delete cascade);-- 或者通过alteralter table course add constraintcs_tno foreign key(tno) references teacher(tno) on delete cascade;

-- 移除约束alter table course drop constraint cs_tno;补充: unique 和not null

create table tbl1(name1 varchar(10), num1 varchar(10),

constraint cs_num1 unique(num1));-- 或者create table tbl1(name1 varchar(10), num1 varchar(10) unique);-- 之后再添alter table tbl1 add constraint cs_num1 unique(num1);alter table tbl1 drop constraint ;-- ⾮空约束create table tbl1(name1 varchar(10), num1 varchar(10) not null);-- 新增⾮空约束alter table tbl1 modify num1 not null;-- 删除⾮空约束 不是⽤dropalter table tbl1 modify num1 null;

(2) table_constr 表约束, ⽤于多列或者元组的值进⾏约束create table student ( sno char(5) not null unique,sname char(5),ssex char(2) constraint ctssex check(ssex='男' or ssex='⼥') ,sage integer check(sage>1 and sage<150).dno char(3) references dept (dno) on delete cascade,sclass char(5),primary key(sno));

--primary key(sno) 可以放在sno 这⼀列的后⾯, 也可以放在最后这⾥, 看成是表约束create table course (cno char(3), cname char(10), chours integer,credit float(1) constraint ctcredit check(credit>=1.0 and credit <=6.0),tno char(3) references teacher(tno) on delete cascade,primary key (cno), constraint ctcc check(chours/credit=12));-- 严格约束12课时对应1个学分create table sc(sno char(5), cno char(3),score float(1) constraint ctscore check(score>=0.0 and score<=100.0),foreign key(sno) references student(sno) on delete cascade,foreign key(cno) references course(cno) on delete cascade);注意: check 后⾯的条件可以是select from where 语句create table sc(sno char(5) check (sno in (select sno from student)),

cno char(3), check(cno in (select cno from course)), --相当于外键score float(1) constraint ctscore check(score>=0.0 and score<=100.0);注意: create table 中的约束条件 可以在后⾯根据需要进⾏撤销 ,也可以追加约束alter table tablename +

add 追加约束, 也可以增加新的⼀列drop 删除⼀列的约束,或者删除⼀列,modify 修改alter table sc drop constraint ctscore; -- 撤销对score的约束ctscore;alter table scmodify ( score float(1) constraint nctscore check(score>0.0 and score<=150.0));

-- 修改约束alter table scadd ( score float(1) constraint nctscore check(score>0.0 and score<=150.0)); -- 增加约束(3) 断⾔ assertion

⼀个断⾔就是⼀个谓词表达式, 它表达了希望数据库总能满⾜的条件, 表约束与 列约束就是⼀些特殊的断⾔.还有复杂的断⾔ create assertion [assertion name] check [predicate]那么之后数据库的每⼀次更新去判断是否违反该断⾔, 断⾔测试增加了数据库维护的负担, 没事不要使⽤!!

例如: 每位教师同⼀时间段不能在两个不同的地⽅上课.实例1: 已知下列4张表borrower(client_name,loan_num) 客户以及他的贷款account(account_num,balance) 账户和余额depositor(account_num, client_name) 账户与客户名loan(loan_num, amount) 每⼀笔贷款现在规定: 每⼀笔贷款 , 要求⾄少这个借款者的账户中有最低余额500元.create assertion balance_cst check(not exists (select * from loanwhere not exists (select * from borrower b, depositor d, account awhere _num=_num _name=_name and

t_num=t_num and e>=500)));实例2: 现有3张表account(branch_name, account_num, balance) 分⾏ 账户 与 余额loan(branch_name,loan_num,amount) 分⾏的每⼀笔贷款branch(branch_name,..) 分⾏信息每⼀个分⾏的贷款总量要⼩于该分⾏所有账户的余额总额 (不存在某⼀个分⾏ 它的贷款额⼤于余额)create assertion sum_cst check(not exists (select * from branch where (select sum(amount) from loanwhere _name=_name) >=(select sum(balance) from account where _name=_name))); (⼆) 动态约束以上 create table 中的表约束与列约束 是静态约束, 下⾯介绍动态约束--> 触发器 trigger动态约束是⼀种过程完整性的约束, 相⽐之下, 之前的create table 的约束是⾮过程性约束动态约束是⼀个程序, 该程序可以在特定的时刻被⾃动触发执⾏: ⽐如在⼀次更新之前,

或者⼀次更新之后的执⾏.

动态约束 intergrity constraint::=(O,P,A,R), O P A R 都需要定义, 再来回顾下

O : 数据集合, 约束的对象 ?: 列, 多列的元组集合P: 谓词条件: 什么样的约束?A: 触发条件: 什么时候检查?R: 响应动作: 不满⾜怎么办?以下是Oracle 的触发器的语法例⼦, 在SQL server中 语法略有差别, 但是思路⼀致. .. 创建触发器的基本语法:create trigger trigger_namebefore| after [insert | delete|update] [of colname] on tablename

for each row| for each statementwhen [search_condition][statement][begin atomic statement; ... end;] --多个条件注意: row , as 可以省略!

实例(1): 当teacher表更新元组时, 控制其⼯资只能涨不能跌create trigger teacher_sal-- 触发器名字before update of salary on teacher -- 作⽤在什么表的什么列referencing new x, old y -- 定义更新前后的值for each row when(<) -- 对每⼀条记录都要检查,

begin --如果违反则执⾏ raise_application_error(-20003,'invalid salary on update'); -- Oracle的错误处理函数, 提⽰⽆效更新 end;

实例(2) : student(sno, sname, sumcourse), sumcourse 表⽰该同学已经学习的课程门数,初始值是0, 以后每修⼀门课都要对其+1, 设计⼀个触发器⾃动完成这个功能.create trigger sumcafter insert on sc -- 对于sc 的新增信息 作出反应referencing new row newI-- 定义更新后的⾏=newifor each row begin -- 执⾏操作 update student set sumsourse=sumcourse+1

where sno=:; -- 这条记录(⾏)对应的学号 end;

实例(3) :student(sno, sname, sage,ssex,scalss) 中某⼀个学⽣变动其主码sno,则在sc 表中该同学的学号也要相应改变create trigger upd_snoafter update of sno on student --指明更新的地⽅referencing old oldi, new newifor each row begin update sc set sno= where sno=: ; end;实例(4) :student(sno, sname, sumcourse)中删除某⼀个学⽣sno时, 在sc 中该学⽣的选课记录也要删除create trigger del_snoafter delete on studentreferencing old oldifor each row begin delete sc where sno=:; end;实例(5) : student(sno, sname, sumcourse)中删除某⼀个学⽣sno时, 在sc 中该学⽣的sno设置为nullcreate trigger del_snoafter delete on studentreferencing old oldifor each row begin update sc set sno=null where sno=:; end;

实例(6) :假设有两张表, dept(dno,dname,dean) ,该表字段是系号 系名 系主任名, 以及teacher(tno,tname,dno,salary) . 现在需要控制 在对dept 的dean 做更新的时候,必须满⾜dean 的⼯资是同⼀系⾥最⾼的, 否则更新报错.create trigger dean_sal

before update of dean on dept -- 对dept 的dean 做更新的时候referencing old oldi , new newi -- 更新前后的新 旧定义for each row when(dean not in(select tname from teacher where dno=: and salary> =

all(select salary from teacher where dno=: )))-- 同系教师⼯资 begin -- 不满⾜条件时 raise_application_error(-20003,'invalid dean on update'); end;