《数据库系统概论》 -- 5 数据库完整性
概念
数据库的完整性是指数据的正确性与相容性
正确性：数据是满足现实世界语义，反映当前状况
相容性：同一数据库对象在不同关系表中的数据是符合逻辑的
作用
防止数据库中存在不符合语义的不正确数据，防范对象对象是不合语义、不正确的数据
实体完整性--列级/表级约束条件
使用 关系模型实体完整性用PRIMARY KEY表示
说明 单码为主码，则该实体完整性约束可作为表级约束条件或列级约束条件
多码为主码，该实体完整性约束只可作为表级约束条件
检查 对基本表进行插入/更新时，RDBMS对该主码值进行检查
违约处理 1.主码值是否唯一，不唯一拒绝插入或修改
2.主码值的各个属性是否为空，为空拒绝插入或修改
其他 由于全表扫描非常耗时，所以RDBMS一般都会自动在主码上建立索引
参照完整性--列级/表级约束条件
使用 关系模型参照完整性用FOREIGN KEY表示
说明 外码定义的被参照关系的码，必须为被参照关系的主码
检查 参照关系新增/修改外码；被参照关系删除/修改主码，均可能破坏参照完整性
违约处理 1.拒绝执行 NO ACTION（默认）
2.级联操作 CASCADE（发生在删除/修改被参照关系时，级联删除/修改参照关系）
3.设置为空值 （发生在删除/修改被参照关系时，设置参照关系中的值设为空）
其他 外码是否可以为空，需根据具体情况具体确定。（如学生表允许学生可以没有选修课程，但是学生选课信息表不允许只有学生，没有选修课程）
可以指定违反参照完整性后的处理措施
CREATE TABLE SC (
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE
/*删除Student的Sno，级联删除SC的Sno*/
ON UPDATE CASCADE,
/*更新Student的Sno，级联更新SC的Sno*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION
/*删除Course的Cno，级联删除SC的Cno*/
ON UPDATE CASCADE
/*删除Course的Cno，级联删除SC的Cno*/
);
用户定义的完整性
属性上的约束条件--列级/表级约束条件
类型
NOT NULL
Sname CHAR(10) NOT NULL,
UNIQUE
Sno CHAR(4) UNIQUE
CHECK (条件表达式)
Grade SMALLINT CHECK (Grade>=0 AND Grade<=100)
违约处理
拒绝执行
元组上的约束条件--表级约束条件
类型
CHECK (条件表达式)
CHECK (Sex='女' OR Sname NOT LIKE 'Ms.%');
违约处理
拒绝执行
完整性约束命名子句--列级/表级约束条件
格式 CONSTRAINT <完整性约束条件名> <完整性约束条件>
优点 方便删除约束
使用 Sno NUMERIC(6) CONSTRAINT C_Sno CHECK (Sno BETWEEN 90000 AND 99999)
ALTER TABLE SC ADD CONSTRAINT C_Sname NOT NULL;
ALTER TABLE SC ADD COLUMN Sage NUMERIC(3) CONSTRAINT C_Sage CHECK (Sage<30);
ALTER TABLE SC DROP CONSTRAINT C_Sno;
ALTER TABLE SC DROP CONSTRAINT C_Sname;
域的创建、完整性约束、使用
创建
CREATE DOMAIN GenderDomain CHAR(2) CHECK (VALUE IN ('男','女'));
完整性约束
ALTER DOMAIN GenderDomian CONSTARINT GD CHECK (VALUE IN ('1','0'));
ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;
使用
定义了这些域后，则可以按照类似使用内置数据类型的方式使用它们
断言
用处 用于定义涉及多个表或聚集操作的复杂完整性约束
创建
格式 CREATE ASSERTION <断言名> <CHECK子句>
举例 限制数据库课程最多60名学生选修
CREATE ASSERTION SC_DB_NUM_ASS CHECK (
60>=(SELECT COUNT(*) FROM SC,Course WHERE Course.Sno=SC.Sno AND Course.Cname='数据库')
);
删除
格式 DROP ASSERTION <断言名>
触发器
概念 触发器又称“事件-条件-动作”规则。当特定的系统事件（如增删改）发生时，对规则定义的条件进行检查，如果条件成立则执行规则中的动作，否则不执行。
说明 动作执行体一般是一段SQL存储过程
格式 CREATEE TRIGGER <触发器名> /*触发器名*/
{BEFORE|AFTER} <触发事件> ON <表名> /*触发器触发时机*/
REFERENCING NEW|OLD ROW|TABLE AS <变量> /*新旧元组变量--仅适用行级触发器；新旧表变量--仅适用表级触发器*/
FOR EACH {ROW|STATEMENT} /*行级/表级触发器*/
[WHEN <触发条件>] <触发动作体> /*触发器执行体*/
说明 表上定义行级/表级触发器，在定义的触发事件发生前/后，该触发器激活，数据库自动执行以下逻辑：判断触发条件，满足则执行动作体，可在动作体中使用触发事件执行前后的表OLD/NEW数据
同一张表上触发器的执行顺序：先BEFORE，后AFTER；多个BEFORE时，先执行创建时间早的触发器（不同RDBMS定义的执行顺序不同）
行级触发器：执行一行前/后，触发该触发器
表级触发器：执行完语句前/后，触发该触发器
NEW ROW--新的元组（新数据）
OLD ROW--旧的元组（旧数据）
NEW TABLE--表有变化的那部分（新增/修改的那部分新数据组成的表）
OLD TABLE--表有变化的那部分（新增/修改的那部分旧数据组成的表）
删除格式 DROP TRIGGER <触发器名> ON <表名>
举例
当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade)中/*行级触发器*/
CREATE TABLE SC_U(
Sno CHAR(9),
Cno CHAR(4),
Oldgrade SMALLINT,
Newgrade SMALLINT,
PRIMARY KEY (Sno,Cno)
);
CREATE TRIGGER SC_GRADE_CHANGE_TRI
AFTER UPDATE ON SC
REFERENCING NEW ROW AS New_SC_Tuple, OLD ROW AS Old_ SC_Tuple
FOR EACH ROW
WHEN ( New_SC_Tuple.Grade>=1.1*Old_ SC_Tuple.Grade )
INSERT INTO SC_U(Sno, Cno, Oldgrade, Newgrade)
VALUES (Old_SC_Tuple.Sno, Old_SC_Tuple.Cno, Old_SC_Tuple.Grade, New_SC_Tuple.Grade);
将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中/*表级触发器*/
CREATE TABLE StudentInsertLog(
Insert_Time TIMESTAMP,
New_Stu_Num SMALLINT
);
CREATE TRIGGER STUDENT_INSERT_TRI
AFTER INSERT ON Student
REFERENCING NEW TABLE AS Insert_Table
FOR EACH STATEMENT
INSERT INTO StudentInsertLog(Insert_Time, New_Stu_num) SELECT now(), COUNT(*) FROM Insert_table;
如果Teacher表中新增/修改操作添加BEFORE行级触发器，如果教授的工资低于4000，则改为4000 /*PL/SQL过程块*/
CREATE TRIGGER Teacher_Wage_Tri
BEFORE INSERT, UPDATE ON Teacher
REFERENCING NEW ROW AS New_Teacher
FOR EACH ROW
BEGIN
IF ( New_Teacher.Job='教授' AND New_Teacher.wage<4000 )
THEN New_Teacher.wage :=4000;
END IF;
END;
附：
1.SQL语句中时间的获得
select now(), sleep(3), now();
| 2013-04-08 20:57:46 | 0 | 2013-04-08 20:57:46 |
select sysdate(), sleep(3), sysdate();
| 2013-04-08 20:58:47 | 0 | 2013-04-08 20:58:50 |
select curdate();
| 2013-04-08 |
select curtime();
| 21:00:11 |
select utc_timestamp(), utc_date(), utc_time(), now();
| 2013-04-08 13:01:32 | 2013-04-08 | 13:01:32 | 2013-04-08 21:01:32 |
