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, 缺省为无操作.
例: 创建一个表 student
create 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); -- 或者通过alter alter table course add constraint cs_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; -- 删除非空约束 不是用drop alter 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 sc modify ( score float(1) constraint nctscore check(score>0.0 and score<=150.0));
-- 修改约束 alter table sc add ( 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 loan where not exists (select * from borrower b, depositor d, account a where loan.loan_num=b.loan_num and b.client_name=d.client_name and a.account_num=d.account_num and a.balance>=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 loan where loan.branch_name=branch.branch_name) >= (select sum(balance) from account where account.branch_name=branch.branch_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_name
before| after [insert | delete|update] [of colname] on tablename
for each row| for each statement
when [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(x.salary<y.salary) -- 对每一条记录都要检查, begin --如果违反则执行 raise_application_error(-20003,'invalid salary on update'); -- Oracle的错误处理函数, 提示无效更新 end;
实例(2) : student(sno, sname, sumcourse), sumcourse 表示该同学已经学习的课程门数,
初始值是0, 以后每修一门课都要对其+1, 设计一个触发器自动完成这个功能.
create trigger sumc after insert on sc -- 对于sc 的新增信息 作出反应 referencing new row newI-- 定义更新后的行=newi for each row begin -- 执行操作 update student set sumsourse=sumcourse+1 where sno=:newi.sno; -- 这条记录(行)对应的学号 end;
实例(3) :student(sno, sname, sage,ssex,scalss) 中某一个学生变动其主码sno
,则在sc 表中该同学的学号也要相应改变
create trigger upd_sno after update of sno on student --指明更新的地方 referencing old oldi, new newi for each row begin update sc set sno=newi.sno where sno=: oldi.sno; end;
实例(4) :student(sno, sname, sumcourse)中删除某一个学生sno时, 在sc 中该学生的
选课记录也要删除
create trigger del_sno after delete on student referencing old oldi for each row begin delete sc where sno=:oldi.sno; end;
实例(5) : student(sno, sname, sumcourse)中删除某一个学生sno时, 在sc 中该学生的sno设置为null
create trigger del_sno after delete on student referencing old oldi for each row begin update sc set sno=null where sno=:oldi.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=:newi.dno and salary> = all(select salary from teacher where dno=:newi.dno )))-- 同系教师工资 begin -- 不满足条件时 raise_application_error(-20003,'invalid dean on update'); end;
