数据库完整性
实体完整性
-
实体完整性：
PRIMARY KEY- 列级 约束条件
CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, /* 在列级定义主码 */ Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sdept CHAR(20) );- 表级约束条件
CREATE TABLE Student (Sno CHAR(9), Sname CHAR(20) NOT NULL, Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (Sno) /* 表级定义主码 */ )- 多个属性构成主码 表级约束条件
CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno,Cno) /* 只能在表级定义主码 */ ) -
实体完整性检查和违约处理
- 检查主码值是否唯一，如果不唯一则拒绝插入或者修改
2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改
- 检查主码值是否唯一，如果不唯一则拒绝插入或者修改
-
参照完整性
FOREIGN KEY外码CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SNALLINT, PRIMARY KEY (Sno,Cno), /* 表级定义实体完整性 */ FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级定义参照完整性 */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级定义参照完整性 */ ) -
参照完整性检查和违约处理
被参照表(如：Student) 参照表(如：SC) 违约处理 可能破坏参照完整性 <--插入元祖 拒绝 可能破坏参照完整性 <--修改外码值 拒绝 删除元祖 --> 可能破坏参照完整性 拒绝/级联/设置空值 修改主码值 --> 可能破坏参照完整性 拒绝/级联/设置空值 可能破坏参照完整性的情况及违约处理
被参照表(如：Student) 参照表(如：SC) 违约处理 可能破坏参照完整性 <--插入元祖 拒绝 可能破坏参照完整性 <--修改外码值 拒绝 删除元祖 --> 可能破坏参照完整性 拒绝/级联/设置空值 修改主码值 --> 可能破坏参照完整性 拒绝/级联/设置空值 拒绝 (NO ACTION) 执行
不允许该操作执行, 该策略一般设置为默认策略级联 (CASCADE) 操作
当删除或修改被参照表(Student)的一个元祖造成了与参照表(SC)的不一致，则删除或修改参照表中的所有造成不一致的元祖-
设置为空值( SET - NULL)
当删除或修改被参考表的一个元祖时造成了不一致，则将参照表中的所有造成不一致的元祖的对应属性设置为空值。显式说明参照完整性违约处理示例
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 CASECADE /* 当删除 student 表汇总的元祖时,级联删除 SC 表中相应的元祖 */ ON UPDATE CASECADE, /* 当更新 studnet 表中的 sno 时, 级联更新 SC 表中相应的元祖 */ FOREIGN KEY(Cno) REFERENCES Course(Cno) /* 在表级定义参照完整性 */ ON DELETE NO ACTION /* 当删除 course 表中的元祖造成了与 SC 表不一致时拒绝删除 */ ON UPDATE CASCADE /* 当更新 course 表中的 cno 时, 级联更新 SC 表中相应的元祖 */ );
-
用户定义完整性
-
属性上的约束条件的定义
- 列值非空 (NOT NULL)
- 列值唯一 (UNIQUE)
- 检查列值 是否 满足一个布尔表达式 (CHECK)
- 不允许取空值
CREATE TABLE SC (Sno CHAR(9) NOT NULL, /* Sno 属性不允许取空值 */ Cno CHAR(4) NOT NULL, /* Grade 属性不允许为空 */ Grade SMALLINT NOT NULL, /* Grade 属性不允许取空值 */ PRIMARY KEY (Sno, Cno) /* 如果在表级定义实体完整性，隐含了 Sno,Cno不允许取空值则在列级不允许取空值的定义就不必写了 */ )- 列唯一
CREATE TABLE DEPT ( Deptno NUMERIC(2), Dname CHAR(9) UNIQUE, /* 要求 Dname列唯一 */ Location CHAR(10), PRIMARY KEY(Deptno) )- 用 CHECK 短语指定列值应该满足的条件
CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, /* 在列级定义主码 */ Sname CHAR(8) NOT NULL, /* Sname 属性不允许取空值 */ Ssex CHAR(8) CHECK (Ssex IN ('男','女')) /* 性别属性Ssex 只允许取'男'或'女' */ Sage SMALLINT, Sdept CHAR(20) )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), PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES Studnet(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ) 属性上的约束条件检查和违约处理
-
元祖上的约束条件的定义
当学生的性别是男时，名字不能以 Ms 开头CREATE TABLE Student (Sno CHAR(9), Sname CHAR(8) NOT NULL, Ssex CHAR(2), Sage SMALLINT, PRIMARY KEY (Sno), CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') /* 定义了元组中Sname 和 Ssex 两个属性值之间的约束条件 */ ): 元组上的约束条件检查和违约处理
当往表中插入元组或修改属性的值时，RDBMS就检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行
-
-
完整性命名子句
- 完整性约束命名子句
CONSTRAINT <完整性约束条件名> [PRIMARY KEY短语|FOREIGN KEY 短语|CHECK短语]
例： 建立学生登记表 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 个约束 C1,C2,C3,C4 StudentKey。
例：建立教师表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 EMPFKey FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno),
CONSTRAINTS C1 CHECK(Sal + Deduct >= 3000)
);
-
修改表中的完整性限制
解除表 Student 中 性别限制ALTER TABLE Student DROP CONSTRAINT C4修改表 Student 中 的约束条件， 要求学号改为 900000-999999 之间，年龄由小于30 改为小于 40.
先删除原来的约束条件，再增加新的约束条件
ALTER TABLE Student Drop CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 9000 000 AND 999 999), ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK( Sage < 40 );
-
域中的完整性限制
CREATE DOMAIN 名字 类型 CHECK语句例: 建立一个性别域，并声明性别域的取值范围
CREATE DOMAIN GenderDemain CHAR(2) CHECK(VALUE IN ('男'，'女'))；例：建立一个性别域 GenderDomain，并对其中的限制命名
CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK (VALUE IN ('男'，'女'));例: 删除域 GenderMain 的限制条件 GD
ALTER DOMAIN GenderDomian DROP CONSTRAINT GD;例：在域GenderDomian 上增加限制条件 GDD
ALTER DOMAIN GenderDomian ADD CONSTRAINT GDD CHECK (VALUE IN ('1','0'));
触发器 Trigger
-
定义触发器
CREATE TRIGGER <触发器名字> |BEFORE|AFTER| <触发器事件> ON <表名> FOR EACH |ROW |STATEMENT| [WHEN <触发条件>] 触发动作体1.谁可以创建触发器?
表的创建者
2. 一张表可以创建无数个触发器么?
只能创建有限个触发器
3. 和触发器关联的表 称为目标表
4. 触发事件怎么理解?
触发事件可以是 INSERT/DELETE/UPDATE 也可以是这几个事件的组合，如 INSERT OR DELETE 等， UPDATE 后面还可以有 OF<触发列...> ，即进一步指明修改哪些列时，触发器激活。
5. 触发器的类型
触发器按照所触发动作的间隔尺寸可以分为行级触发器(FOR EACH ROW) 和 语句级触发器（FOR EACH STATEMENT）.
FOR EACH ROW 凡是该row 的值发生变化，就会触发，触发多次
FOR EACH STATEMENT 无论多少条数据发生变化，只触发一次
6. 触发条件
当存在 WHEN 时， 只有满足 WHEN 后面的条件 触发动作执行
没有 WHEN 条件存在时， 默认触发动作执行。
7.触发动作体
触发动作体 既可以是一个匿名 PL/SQL 过程块，也可以是对已创建存储过程的调用，如果是行触发器，在两种情况下，用户都可以在过程体中使用 NEW 和 OLD 引用 UPDATE/INSERT 事件之后的新值和 UPDATE/DELETE 事件之前的旧值，如果是语句级触发器，则不能在触发动作体中使用 NEW 或 OLD 进行引用。
如果触发动作体执行失败，激活触发器的时间就会终止执行，触发器的目标或触发器可能影响的其他对象不发生任何变化。
例：定义一个BEFORE 行级触发器，为教师表 Teacher 定义完整性规则“教授的工资不得低于4000 元，如果低于4000元 自动改为4000元”。
CREATE TRIGGER Insert_Ur_Update_Sal /* 在教室表 Teacher 上定义触发器 */
BEFORE INSERT OR UPDATE ON Teacher /* 触发事件是插入或更新操作 */
FOR EACH ROW /* 这是行级触发器 */
AS BEGIN /* 定义触发动作体，这是一个 PL/SQL 过程块 */
IF (new.pJub='教授') AND (new.Sal < 4000) THEN /* 因为是行级触发器，可在 */
new.Sal := 4000; /* 过程中使用插入或更新操作后的新值 */
END IF
END; /* 触发动作体结束 */
例：定义 AFTER 行级触发器，当教师表 Teacher 的工资发生变化后就自动在工资变化表 Sal_log 中增加一条相应记录
CREATE TABLE Sal_log
(Eno NUMERIC(4) refereace teacher(eno)
Sal NUMERIC(7,2),
Username char(10),
Date TIMESTAMP
);
CREATE TRIGGER Insert_Sal /* 建立了一个触发器 */
AFTER INSERT ON Teacher /* 触发器事件是 INSERT */
FOR EACH ROW
AS BEGIN
INSERT INTO Sal_log VALUES(
new.Eno, new.Sal, CURRENT_USER, CURRENT_TIMESTAMP);
END;
CREATE TRIGGER Update_Sal /* 建立一个触发器 */
AFTER UPDATE ON Teacher /* 触发器事件是 UPDATE */
FOR EACH ROW
IF(new.Sal<>old.Sal) THEN INSERT INTO Sal_log VALUES(
new.Eno, new.Sal, CURRENT_USER, CURRENT_TIMESTAMP);
END IF;
END;
-
激活触发器
触发器执行，由触发事件激活，由数据库服务器自动执行。同一张表上多个触发器激活时遵循如下的执行顺序
- 执行该表上的 BEFORE 触发器
- 激活触发器的 SQL 语句
- 执行该表上的 AFTER 触发器
-
删除触发器
语句如下：DROP TRIGGER <触发器名> ON <表名>触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除
