第五章完整性

一，实体完整性

【例5.1】将Student表中的Sno属性定义为码

CREATE TABLE Student
(
	Sno CHAR(9)PRIMARY,--在列级定义主码
	Sname CHAR(20) NOT NULL,
	Ssex CHAR(2),
	Sage SMALLINT
	Sdept CHAR(20)
);
--OR
CREATE TABLE Student
(
	Sno CHAR(9)PRIMARY,
	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.3]定义SC中的参照完整性

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
 Cno CAHR(4) NOT NULL,
 Grade SMALLINT,
 PRIMARY KEY(Sno,Cno),--在表级定义实体完整性
 FOREIGN KEY REFERENCES Student(Sno),--在表级定义参照完整性
 FOREIGN KEY REFERENCES Course(Cno)--在表级定义参照完整性
);

[例5.4] 显式说明参照完整性的违约处理示例

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
 Cno CHAR(2) 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 ON ACTION --当删除course 表中的元组造成了与SC表不一致时拒绝删除
  ON UPDATE CASCADE --当更新course表中的cno时，级联更新SC表中相应的元组
);

三，用户定义的完整性

1.属性上的约束条件

针对某一具体应用的数据必须满足的语义要求
CREATE TABLE时定义属性上的约束条件
列值非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足一个条件表达式（CHECK）

（1）不允许取空值

[例5.5] 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT NOT NULL,
PRIMARY KEY (Sno,Cno)
);

（2）列值唯一

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

CREATE TABLE DEPT
(Deptno NUMERIC(2),
 Dname CHAR(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('男','女'))
 Sage SMALLINT,
 Sdept CHAR(20)
);

[例5.8] SC表的Grade的值应该在0和100之间。

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
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)
);

2.元组上的约束条件

在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制

[例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两个属性值之间的约束条件
);

• 性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立;
• 当性别是男性时，要通过检查则名字一定不能以Ms.打头

四，完整性约束命名字句

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(9)
 CONSTRAINT C2 NOT NULL,
 Sage NUMERIC(3)
 CONSTRAINT C3 CHECK(Sage<30),
 Sex CHAR(2)
 CONSTRAINT C4 CHECK (Ssex IN ('男','女')),
 CONSTRAINT Studentkey PRIMARY 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.修改表中的完整性限制

[例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);

五，断言

1.创建断言的语句格式

CREATE ASSERTION<断言名><CHECK 子句> 每个断言都被赋予一个名字，<CHECK
子句>中的约束条件与WHERE子句的条件表达式类似。

只有标准SQL语句
[例5.18] 限制数据库课程最多60名学生选修

CREATE ASSERTION ASS_SC_CNUM1
CHECK (60>=ALL(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 <断言名>;

六，触发器（Trigger）

功能介绍

1.任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
2.触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

1.定义触发器

CREATE TRIGGER语法格式：
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

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%则将此次操作记录到下面表中：

SQL

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
	OLD row AS OldTuple
	NEW row AS NewTuple --REFERENCING指出引用变量，但是T-SQL并不支持
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)

T-SQL

CREATE TRIGGER SC_T
ON SC
AFTER UPDATE
AS
BEGIN
DECLARE @Sno CHAR(9),@Cno CHAR(4),@OLDGRADE SMALLINT,@NEWGRADE SMALLINT
SELECT @Sno=Sno FROM INSERTED
SELECT @Cno=Cno FROM INSERTED
SELECT @OLDGRADE=GRADE FROM DELETED
SELECT @NEWGRADE=GRADE FROM INSERTED
IF(@NEWGRADE>=1.1*@OLDGRADE)
BEGIN
INSERT INTO SC_U
VALUES(@Sno,@Cno,@OLDGRADE,@NEWGRADE)
END;
END;

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

SQL

CREATE TRIGGER Student_CNT
AFTER INSERT ON Student
REFERENCING 
NEW TABLE AS DELTA
FOR EACH STATEMENT
INSERT INTO Student StudentINSERTLOG(NUMBERS)
SELECT CNT(*)FROM DELTA

T-SQL

CREATE TRIGGER Student_Count
ON Student 
AFTER INSERT
AS
INSERT 
INTO StudentInsertLog(Number)
SELECT COUNT(*) FROM Student;

[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

SQL

CREATE TRIGGER INSERT_OR_UPDATE_SAL  --对教师表插入或更新时激活触发器
BEFORE INSERT OR UPDATE ON TEACHER    --before 触发事件
REFERENCING NEW ROW AS NEWTUPLE      
FOR EACH ROW             --行触发器
BEGIN                         --定义触发动作体，这是一个PL/SQL过程块
IF(NEWTUPLE.Job='教授')AND (NEWTUPLE.SAL<4000)
THEN NEWTUPLE.SAL:=4000;   --因为是行触发器，可在过程体中
END IF;
END;                       --触发动作结束

T-SQL

CREATE TRIGGER Insert_or_Update_Sal
ON Teacher 
FOR INSERT,UPDATE 
AS
DECLARE @Newtuple INT
DECLARE @Job CHAR(10)
IF (UPDATE(Salary))
SELECT @Salary=Salary FROM INSERTED
SELECT @Job=Job FROM Teacher
IF(@Job='教授')AND(@Salary<4000)
UPDATE Teacher
SET Salary=4000
WHERE Job='教授'AND Salary<4000;

删除触发器

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除

DROP TRIGGER <触发器>ON<表名>

七，存储过程

1.创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

SQL

CREATE OR REPLACE PROCEDURE TRANSFER
(INACCOUNT INT,OUTACCOUNT INT,AMOUNT FLOAT)
AS DECLARE
TOTALDEPOSITOUT FLOAT;
TOTALDEPOSITIN FLOAT;
INACCOUNTNUM INT;
BEGIN
SELECT TOTAL INTO TOTALDEPOSITOUT
FROM ACCOUNT 
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=IN ACCOUNT；
IF INACCOUNT IS NULL THEN
ROLLBACK;
RETURN;
END IF;
UPDATE ACCOUNT SET TOTAL=TATAL-AMOUNT WHERE ACCOUNTNUM=OUTACCOUNT;
UPDATE ACCOUNT SET TOTAL=TATAL+AMOUNT WHERE ACCOUNTNUM=INACCOUNT;
COMMIT;
END;

T-SQL

IF(EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='P_T'))
DROP PROCEDURE P_T
GO
CREATE PROCEDURE P_T
@INID CHAR(3),@OUTID CHAR(3),@AMOUNT FLOAT
AS
BEGIN
TRANSACTION TRANS
DECLARE @TOTALOUT FLOAT,@TOTALIN FLOAT,@OUTIDNUM CHAR(3);   --查询转出账户余额
SELECT @TOTALOUT=TOTAL FROM T_RE WHERE ANUM=@OUTID;
IF(@TOTALOUT IS NULL) BEGIN PRINT'账户不存在或者无余额'
ROLLBACK TRANSACTION TRANS;RETURN ; END; --回滚事务
IF(@TOTALOUT<@AMOUNT)BEGIN PRINT'账户余额不足' 
ROLLBACK TRANSACTION TRANS;RETURN;END;
SELECT @TOTALIN=ANUM FROM T_RE WHERE ANUM=@INID;
IF(@TOTALIN IS NULL) BEGIN PRINT'转入账户不存在'
ROLLBACK TRANSACTION TRANS;RETURN;END;
BEGIN
UPDATE T_RE SET TOTAL=TOTAL-@AMOUNT WHERE ANUM=@OUTID;
UPDATE T_RE SET TOTAL=TOTAL+@AMOUNT WHERE ANUM=@INID;
PRINT'请取走银行卡'
COMMIT TRANSACTION TRANS;
RETURN;
END;

转账

SELECT * FROM T_RE;
EXEC	P_T
		@INID = 101,	--转入账户
		@OUTID = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM T_RE;

2.执行存储过程

CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);

[例8.10]从账户01转100元到02账户中

CALL PROCEDURE TRANSFER (01,02,100);

3.修改存储过程

使用ALTER PROCEDURE 重命名一个存储过程

ALTER PROCEDURE 过程1 RENAME TO 过程2；

4.删除存储过程

DROP PROCEDURE 过程名();

八，函数

1、函数的定义语句格式。

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,...])RETURNS<类型>
AS <过程化SQL块>;

2、函数的执行语句格式。

CALL/SELECT 函数名 ([参数1,参数2,...]);

3、修改函数。

重命名一个自定义函数

ALTER FUNCTION 过程名1 RENAME TO 过程名2；

重新编译一个函数

ALTER FUNCTION 函数名 COMPILE;