本文详细介绍了数据库中的完整性约束概念，包括实体完整性、参照完整性和用户定义的完整性。通过具体实例展示了如何使用SQL语句来实现这些约束，以及如何通过触发器和存储过程来增强数据库的安全性和一致性。
5.1 实体完整性
关系模型的实体完整性
CREATE TABLE中用PRIMARY KEY定义
[例5.1] 将Student表中的Sno属性定义为码
--列级定义主码
create table Student
(Sno char(9) primary key,
Sname char(20) not null,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
--表级定义主码
create table Student2
(Sno char (9),
Sname char(20) not null,
Ssex char(2),
Sage smallint,
Sdept char(20)
primary key(Sno)
);
[例5.2] 将SC表中的Sno，Cno属性组定义为码
create table SC
(Sno char(9) not null,
Cno char(4) not null,
Grade smallint,
primary key (Sno,Cno)--只能在表级定义主码
);可以联想实体完整性
5.2 参照完整性
[例5.3]定义SC中的参照完整性
create table SC2
(Sno char(9) not null,
Cno char(4) not null,
grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key (Cno)references Course(Cno)
);
[例5.4] 显式说明参照完整性的违约处理示例
create table SC3
(Sno char(9) not null,
Cno char(4) not null,
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Studen(Sno)
on delete cascade --级联删除SC3表中相应的元组
on update cascade --级联更新SC3表中相应的元组
foreign key (Cno) references Course(Cno)
on delete on action --当删除course 表中的元组造成了与SC表不一致时拒绝删除
on update cascade --当更新course表中的cno时，级联更新SC表中相应的元组
);5.3 用户定义的完整性
- 针对某一具体应用的数据必须满足的语义要求
- CREATE TABLE时定义属性上的约束条件
列值非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足一个条件表达式（CHECK）
（1）不允许取空值
create table SC4
(Sno char(9) not null,
Cno char (4) not null,
Grade smallint not null,
primary key(Sno,Cno)
--如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在
--列级不允许取空值的定义 可以不写
);[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
create table Dept
(Deptno numeric(2)
Dname char(9) unique not null,
--Dname 列值唯一 并且不能取空
location char(10),
primary lkey(Deptno)
);（3）用CHECK短语指定列值应该满足的条件
[例5.7] Student表的Ssex只允许取“男”或“女”。
create char(9)primary key
Sname char(8) not null,
Ssex char(2) check(Ssex in('男','女')),
Sage smallint,
Sdept char(20)
);[例5.8] SC表的Grade的值应该在0和100之间。
create table SC
(Sno char(9),
Cno char(4),
grade smallint check (Grade>=0 and Grade<=100),
--Grade 取值范围是0-100
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key(Cno)references Course(Cno)
);元组上约束条件：
在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
[例5.9]当学生的性别是男时，其名字不能以Ms.打头。
create table Student4
( Sno char(9),
Sname char(8) not null,
Ssex char(2),
Sage smallint,
Sdept char(20),
primary key(Sno),
check (Ssex='女'or Sname not like 'Ms.%')
--定义了元组中Sname 和 Ssex 两个属性值之间的而约束条件
);性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立;
当性别是男性时，要通过检查则名字一定不能以Ms.打头
5.4 完整性约束命名字句
1.完整性约束命名子句
CONSTRAINT <完整性约束条件名><完整性约束条件>
<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等
[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。（常用 要会！)
create table Student
( Sno numeric(6)
constraint C1 check(Sno between 90000 and 99999),
Sname char(20)
constraint C2 not null,
Sage numeric C3 check (Sage<30),
Ssex char(2)
constraint C4 check(Ssex in('男','女')),
constraint StudentKey primary e=key(Sno)
);
--在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。
[例5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。
create table Teacher
( Eno numeric(4) primary key
Ename char(10)
Job char(8)
Sal numeric(7,2),
Deduct numeric(7,2),
Deptno numeric(2),
constraint Teacherfkey foreign key(Deptno)
references Dept(Deptno),
constraint C1 check(Sal+Deduct>=3000)
);2. 修改表中的完整性限制
使用ALTER TABLE语句修改表中的完整性限制
[例5.12]去掉例5.10 Student表中对性别的限制。
alter table Student
drop constraint C4;[例5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
alter table Student
drop constraint C1;
alter table Student
add constraint C1 check (Sno between 900000 and 99999);
alter table Student
drop constraint C3;
alter table Student
add constraint C3 check (Sage<40);5.6 断言
1. 创建断言的语句格式
CREATE ASSERTION<断言名><CHECK 子句>
每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。
[例5.18] 限制数据库课程最多60名学生选修
标准Sql
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60 >= (select count(*)
From Course,SC
Where SC.Cno=Course.Cno and Course.Cname ='数据库')
);
[例5.19]限制每一门课程最多60名学生选修
CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60 >= ALL (SELECT count(*) FROM SC
GROUP BY cno)
);
/*此断言的谓词，涉及聚集操作count 和分组函数group by 的SQL语句*/
2. 删除断言的语句格式为
DROP ASSERTION <断言名>;
注意：T-SQL 中没有 ASSERTION 功能。
类似的有RULE，但使用方法不同：
上下文中不允许使用子查询，只允许使用标量表达式。
例如：
CREATE RULE sex_rule
AS @sex in ('男','女')
使用Constraint基本能完成功能，不建议使用RULE。
以后再研究研究吧 目前没看懂。。。
触发器
1、定义触发器
CREATE TRIGGER语法格式
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>
当特定的系统事件发生时，对规则的条件进行检查。
如果条件成立则执行规则中的动作，否则不执行该动作。
规则中的动作体可以很复杂，通常是一段SQL存储过程
2.触发事件
- 触发事件可以是INSERT、DELETE或UPDATE
也可以是这几个事件的组合
- 还可以UPDATE OF<触发列，...>，即进一步指明修改哪些列时激活触发器
- AFTER/BEFORE是触发的时机
AFTER表示在触发事件的操作执行之后激活触发器
BEFORE表示在触发事件的操作执行之前激活触发器
3.触发器类型
- 行级触发器（FOR EACH ROW）
- 语句级触发器（FOR EACH STATEMENT）
例如,在例5.11的TEACHER表上创建一个AFTER UPDATE触发器，触发事件是UPDATE语句：
UPDATE TEACHER SET Deptno=5;假设表TEACHER有1000行
- 如果是语句级触发器，那么执行完该语句后，触发动作只发生1次
- 如果是行级触发器，触发动作将执行1000次
注意：不同的RDBMS产品触发器语法各不相同
[例5.21]当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：
create trigger Sc_t
after update of Grade on SC
referencing
old row as OldTuple,
new row as NewTuple
for each row
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);
触发条件是update并且由for each row子句，那么可以引用的变量有oldrow 和 newrow，分别表示修改前后的元组。
T-Sql
create trigger Sc_t
on SC after update
as
begin
declare @OldTuple int;
declare @NewTuple int;
select @OldTuple= Grade from inserted
select @NewTuple=Grade from inserted;
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);
[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。
create trigger Student_Count
after insert on Student
new table as Delta
for each statement
insert into Studentinsertlog(Numbers)
select count(*) from delta;
T-Sql
create table StudentInsertLog(
Numbers int
);
--记得分开写不然会像下面一样报错
create trigger Student_Count
on Student
after insert
as insert into StudentInsertLog(Numbers)
select count(*) from Student;
执行
insert into Student
values('20151167','My','男',12,'CS');
select * from StudentInsertLog;
[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。
create trigger insert_or_Update_Sal
before insert or update on Teacher
for each row
begin
if(new.Job='教授')and(new.sal<4000)
then new.Sal :=4000;
end if;
end;注意标准sql 的“=”
4.激活触发器
触发器的执行，是由触发事件激活的，并由数据库服务器自动执行！
一个数据表上可能定义了多个触发器，遵循如下的执行顺序:
（1） 执行该表上的BEFORE触发器;
（2） 激活触发器的SQL语句;
（3） 执行该表上的AFTER触发器。
5.删除触发器
DROP TRIGGER <触发器名> ON <表名>;
存储过程和函数
1.存储过程
存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。
优点：
（1）运行效率高
（2）降低了客户机和服务器之间的通信量
（3）方便实施企业规则
（1）创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；
[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
(这里不知哪里错误提示列名accountnum无效 Account 表中是有的。。。。。）
create procedure Proc_TRANSFER (
--定义存储过程为 transfer
@inAccount int,
@outAccount int,
@amount float)
--形参
as
begin transaction trans
declare
@totalDepositOut float,--定义变量
@totalDepositin float,
@inAccountum int;
select @totalDepositOut=total from Account where accountnum = @outAccount;
if @totalDepositOut is null --1.如果转出账户不存在或账户中没有存款
begin
print '转出账户不存在或者账户余额为0'
rollback transaction trans; --回滚事务
return
end ;
if @totalDepositOut < @amount --2.账户余额不足
begin
print '账户余额不足'
rollback transaction trans;
return;
end
select @inAccount =accountnum from Account
where accountnum = @inAccount;
if @inAccount is null
begin
print '转入账户不存在'
rollback transaction trans;
return ;
end ;
begin
update Account set total =total-@amount
where accoutnum =@outAccount;
update account set total = total+@amount
where accountnum =@inAccount;
print '转账成功'
commit transaction trans;
return ;
end;（2）执行存储过程
[例8.9] 从账户01003815868转10000元到01003813828账户中。
sql
call procedure
transfer (01003813828,01003815868,10000);
T-sql
exec Proc_TRANSFER
@inAccount = 01003813828, --转入账户
@outAccount = 01003815868, --转出账户
@amount = 10000 --转出金额
select * from Account;（3）修改存储过程
ALTER PROCEDURE 过程名1 RENAME TO 过程名2;
（4）删除存储过程
DROP PROCEDURE 过程名()；
扫一扫
1520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
