SQL 数据库完整性

实体完整性

定义实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIAMRY 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 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)	
	--只能在表级定义主码
)

实体完整性检查和违约处理

关系数据库管理系统，会按照实体完整性规则自行进行检查
（1）检查主码值是否唯一
（2）检查主码的各个属性是否为空

参照完整性

定义参照完整性

关系模型的参照完整性在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)
);

参照完整性检查和违约处理

上述的不一致发生时，系统可以采用以下策略加以处理：
（1）拒绝执行(NO ACTION)
（2）级联操作(CASCADE)
（3）设置为空值

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

CREATE TABLE SC(
	Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno),
	PRIMARY KEY(Sno) REFERENCES Student(Sno)
		ON DELETE CASCADE
		ON UPDATE CASCADE
	PRIMARY KEY(Cno) REFERENCES Course(Cno)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

用户定义的完整性

属性上约束条件的定义

在CREATE TABLE中定义属性的同时，可以添加如下约束条件：

列值非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足一个条件表达式（CHECK语句）

不允许取空值

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

列值唯一

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

CREATE TABLE DEPT(
	Deptno NUMERIC(2),
	Dname CHAR(9) UNIQUE NOT NULL,
	Location CHAR(10),
	PRIMARY KEY(Deptno)
);

用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),
	Cno CHAR(4),
	Grade SMALLINT CHECK(Grade >= 0 AND Grade <= 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 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.%')
	--等价于
	--CHECK(Ssex = '男' AND Sname NOT LIKE 'Ms.%')
);

完整性约束命名子句

完整性约束命名子句

CONSTRAINT <完整性约束条件名> <完整性约束条件>

<完整性约束条件>包括NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK短语等

例5.10：建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”

CREATE TABLE Student(
	Sno NUMERIC(9)
		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元。应发工资是工资列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(7,2),
	CONSTRAINT TEACHERFKey FOREIGN KEY(Deptno) REFERENCES DEPT(Deptno),
	CONSTRAINT C1 CHECK (Sal + Deduct >= 3000)
);

修改表中的完整性限制

可以使用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 999999);
ALTER TABLE Student
	DROP COSTRAINT C3;
ALTER TABLE Student
	ADD CONSTRAINT C3 CHECK(Sage < 40);

修改完整性性质的如何，如果是改变原有的条件的话，先是去掉原有限制，再添加现在需要的限制

断言

在SQL Server中好像并不支持断言ASSERTION语句，运行之后便会报错

创建断言的语句格式

CREATE ASSERTION <断言名> <CHECK子句>

例5.19：限制数据库课程最多60名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM
CHECK(
	60 >= (
		SELECT count(*)
		FROM Course,SC
		WHERE SC.CNO = COURSE.CNO AND COURSE.CNAME= '数据库'
	)
);

例5.20：限制每个学期每一门课程最多60名学生选修

ALTER TABLE SC 
ADD TERM DATE;
--先修改SC表，增加DATE类型的属性TERM
CREATE ASSERTION ASSE_SC_CNUM2
CHECK (60 >=(
		SELECT COUNT(*)
		FROM SC
		GROUP BY Cno,TERM
	)
); 

触发器

触发器：任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。

定义触发器

触发器又叫事件-条件-动作规则。当特定的系统事件（对一个表的增、删、改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立的话，执行规则中规定的动作，否则，不执行。

CREATE TRIGGER的命令建立触发器：

CREATE TRIGGER <触发器名>
{BEFORE|AFTER} <触发事件> ON <表名>
REFERENCES NEW|OLD ROW AS <常量>
FOR EACH{ROW|STATEMENT}
[WHEN <触发事件>] <触发动作体>

1. 只有表的创建者才可以在表上创建触发器，并且一个数据库只能创建一定数量的触发器，数量是由创建数据库的人指定的
2. 触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的，并且触发器名和表名必须在同一模式下
3. 触发器只能定义在基本表上，但不能定义在视图上
4. 触发事件可以是INSERT、DELETE、UPDATE，也可以是这几个事件的组合
5. 触发器可分为：行级触发器（执行n次）和 语句级触发器

例5.21：当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U（Sno、Cno、Oldgrade、Newgrade）中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
	OLDROW AS OldTuple
	NEWROW 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)

例5.22：将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog

CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
	NEW TABLE DELTA
FOR EACH STATEMENT
	INSERT INTO StudentInsertLog(Numbers)
	SELECT COUNT(*) FROM DELTA

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

CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
REFERENCING NEW row AS newTuple
FOR EACH ROW
BEGIN
	IF(newtuple.Job='教授') AND (newtuple.Sal<4000)
		THEN newtuple.Sal := 4000;
	END IF
END;

激活触发器

触发器激活时遵循如下的执行顺序：

1. 执行该表上的BEFORE触发器
2. 激活触发器的SQL语句
3. 执行该表上的AFTER触发器

删除触发器

删除触发器的SQL语句如下：

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