本文深入探讨SQL中的完整性约束，包括实体完整性、参照完整性和用户定义完整性，并讲解了触发器、存储过程和函数的使用，以及如何通过SQL语句实现复杂的业务规则。
本文参考内容地址：
https://blog.youkuaiyun.com/qq_38975453/article/details/104729681
实体完整性：
- 关系模型的实体完整性再create table 中用 primary key定义。
- 对单属性构成的码有两种说明方法：
定义为列级约束条件
定义为表级约束条件 - 对多个属性构成的码，只能定义为表级约束条件；
[例5.1] 将Student表中的Sno属性定义为码
--列级定义主码
create tablr Student
(
Sno char(9) primary key,
Sname char(20) not null,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
--表级定义主码：
create table Student
(
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)
);
参照完整性：
- 在create table 中用foreign key定义哪些列为外码；
- 用references短语指明这些外码参照哪些表的主码；
[例5.3] 定义SC中的参照完整性
create table SC
(
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 SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE /*级联删除SC表中相应的元组*/
ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION
/*当删除Course 表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE
/*当更新Course表中的Cno时，级联更新SC表中相应的元组*/
);
当修改Student表的Sno列时，SC表也会自动同步更新；
用户定义完整性
- 属性上的约束条件的定义：
列值非空（not null）
列值唯一（unique）
检查列值是否满足一个条件表达式（check语句）。
（1）不允许空值：
[例5.5] 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。
create table SC
(
Sno char(9) not null, /*Sno属性不能取空值*/
Cno char(4) not null,
Grade smallint not null,
primary key(Sno, Cno), /* 在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义可不写 */
...
);
（2）列值唯一
[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
create table DEPT
(
Deptno numeric(2),
Dname vhar(9) unique not null, /*要求Dname列值唯一, 并且不能取空值*/
Location char(10),
primary key (Deptno)
);
（3）用check短语指定劣质应该满足的条件：
[例5.7] Student表的Ssex只允许取“男”或“女”。
create table Student
(
Sno char(9) primary key,
Sname char(8) not null,
Ssex char(2) check(Ssex in ('男','女')), /*性别属性Ssex只允许取'男'或'女' */
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)
);
元组上的约束条件
- 元组级别的限制可以设置不同属性之间的取值的相互约束条件。
[例5.9] 当学生的性别是男时，其名字不能以Ms.打头。
create table Student
(
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两个属性值之间的约束条件*/
);
完整性约束命名：
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(3)
constraint C3 check (Sage < 30),
Ssex char(2)
constraint C4 check(Ssex in( '男','女')),
constraint StudentKey primary key(Sno)
);
[例5.11] 建立教师表TEACHER，要求每个教师的应发工资不低于3000元。
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 TEACHERFKry foreign key (Deptno) references DEPT(Deptno),
constraint C1 check(Sal + Deduct >= 3000) /*应发工资是工资列Sal与扣除项Deduct之和。*/
);
[例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 999999),
alter table Student
drop constraint C3;
alter table Student
add constraint C3 check(Sage < 40);
触发器：
- 定义触发器：
create trigger <触发器名>
{before | after} <触发事件> on <表名>
referencing new|old row as<变量>
for each {row | statement}
[when<触发条件>]<触发动作体>
- 删除触发器：
drop trigger <触发器名> on <表名>;
[例5.21] 当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：SC_U（Sno,Cno,Oldgrade,Newgrade）
其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
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，分别表示修改前后的元组。
[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。
create trigger Student_Count
after update on Student
referencing
new table as DELTA
for each statement
insert into StudentInsertLog (Numbers)
select count(*) from DELTA
[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。
create trigger Insert_Or_Update_Sal
before insert or update on Teacher /*触发事件是插入或更新操作*/
for each row /*行级触发器*/
begin /*定义触发动作体，是PL/SQL过程块*/
if(new.Job = '教授') and (new.Sal < 4000)
then new.Sal := 4000;
end if;
end;
在此定义的是before触发器，再插入和更新教师记录前就可以按照触发器的规则调整教授的工资；
过程化sql
存储过程和函数：
- 创建存储过程：
create or replace procedure过程名([参数1,参数2,...]) as <过程化SQL块>；
- 执行存储过程：
call / perform procedure过程名([参数1,参数2,...])；
- 修改存储过程：
alter procedure过程名1 rename to 过程名2;
- 删除存储过程：
drop procedure 过程名()；
[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
create or replace procedure TRANSFER(inAccount int,outAccount int,amount float)
/*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
as declare /*定义变量*/
totalDepositOut float;
totalDepositIn float;
inAccountnum int;
begin /*检查转出账户的余额 */
select Total into totalDepositOut from Accout
where accountnum=outAccount;
if totalDepositOut is null then /*如果转出账户不存在或账户中没有存款*/
rollback; /*回滚事务*/
return
end if;
if totalDepositOut < amount then /*如果账户存款不足*/
rollback; /*回滚事务*/
return
end if
select Accountnum into inAccountnum from Account
where accountnum = inAccount;
if inAccount is null then /*如果转入账户不存在*/
rollback; /*回滚事务*/
return;
end if;
update Account set total = total - amount
where accountnum = outAccount; /* 修改转出账户余额，减去转出额 */
update Account set total = total + amount
where accountnum = inAccount; /* 修改转入账户余额，增加转入额 */
commit; /* 提交转账事务 */
end;
[例8.9] 从账户01003815868转10000元到01003813828账户中。
call procedure transfer(01003813828,01003815868,10000);
以上两道题目的语句在SQL server中无法运行，如果在SQL server运行需要用到以下语句：
--建立新表Account ,写入两个用户
DROP TABLE IF EXISTS Account;
CREATE TABLE Account
(
accountnum CHAR(3), -- 账户编号
total FLOAT -- 账户余额
);
INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(102,100);
SELECT * FROM Account
--建立存储过程
IF (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
DROP PROCEDURE Proc_TRANSFER
GO
CREATE PROCEDURE Proc_TRANSFER
@inAccount INT,@outAccount INT,@amount FLOAT
/*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
AS
BEGIN TRANSACTION TRANS
DECLARE /*定义变量*/
@totalDepositOut Float,
@totalDepositIn Float,
@inAccountnum INT;
/*检查转出账户的余额 */
SELECT @totalDepositOut = total FROM Account WHERE accountnum = @outAccount;
/*如果转出账户不存在或账户中没有存款*/
IF @totalDepositOut IS NULL
BEGIN
PRINT '转出账户不存在或账户中没有存款'
ROLLBACK TRANSACTION TRANS; /*回滚事务*/
RETURN;
END;
/*如果账户存款不足*/
IF @totalDepositOut < @amount
BEGIN
PRINT '账户存款不足'
ROLLBACK TRANSACTION TRANS; /*回滚事务*/
RETURN;
END
/*检查转入账户的状态 */
SELECT @inAccountnum = accountnum FROM Account WHERE accountnum = @inAccount;
/*如果转入账户不存在*/
IF @inAccountnum IS NULL
BEGIN
PRINT '转入账户不存在'
ROLLBACK TRANSACTION TRANS; /*回滚事务*/
RETURN;
END;
/*如果条件都没有异常，开始转账。*/
BEGIN
UPDATE Account SET total = total - @amount WHERE accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
UPDATE Account SET total = total + @amount WHERE accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
PRINT '转账完成，请取走银行卡'
COMMIT TRANSACTION TRANS; /* 提交转账事务 */
RETURN;
END
--执行存储过程：
SELECT * FROM Account
EXEC Proc_TRANSFER
@inAccount = 101, --转入账户
@outAccount = 102, --转出账户
@amount = 50 --转出金额
SELECT * FROM Account
扫一扫
640
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
