数据库学习|数据库系统概论学习笔记——数据完整性数据库|mysql

数据库完整性 1、实体完整性 1.1 定义实体完整性

表级约束
单属性码表级约束

CREATE TABLE Student (Sno CHAR(9), Sname CHAR(20) NOT NULL, Ssex CHAR(2), PRIMARY KEY (Sno) /* 单属性主码表级约束 */ );

多属性码表级约束

CREATE TABLE SC (SNO CHAR(9) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT, PRIMARY KEY(SNO,CNO) /* 多个属性构成的主码只能有一种说明方法，即表级约束 */ );
列级约束

CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, /* 主码列级约束 */ Sname CHAR(20) NOT NULL, Ssex CHAR(2), PRIMARY KEY (Sno) );

1.2 实体完整性检查和违约处理
1.2.1 检查内容：

检查主码值是否唯一，不唯一则拒绝插入或修改。
检查主码的各个属性是否为空（多属性主码），为空则不允许进行操作。

2、参照完整性

参照完整性主要用于外键的参照关系

2.1 定义参照完整性
外码参照完整性

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

2.2 参照完整性检查和违约处理
2.2.1 破坏参照完整性的情况

高亮部分为引起参照完整性被破坏的原因

被参照表	参照表（例如SC)	违约处理
可能破坏参照完整性	插入元组	拒绝
可能破坏参照完整性	修改外码值	拒绝
删除元组	可能破坏参照完整性	拒绝/级联删除/设置为空值
修改外码值	可能破坏参照完整性	拒绝/级联删除/设置为空值

2.2.2 应对策略

拒绝（NO ACTION）执行

拒绝执行该操作
级联（CASCADE）操作

即破坏参照完整性后，删除或修改所有相关元组
设置为空值

例子：

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 ON UPDATE CASCADE,/* 级联操作 */ FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /* 拒绝操作 */ ON UPDATE CASCADE );

3、用户定义的完整性

包括属性、元组上的约束条件

3.1 属性上的约束条件

列值非空（NOT 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), FOREIGN KEY(Cno) REFERENCES Course(Cno) );
列值唯一（UNIQUE）

CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4)UNIQUE NOT NULL,/* 非空唯一约束 */ Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) );
检查列值是否满足一个条件表达式（用CHECK）

CREATE TABLE Student (Sno CHAR(9), Sname CHAR(20) NOT NULL, Ssex CHAR(2) CHECK(Ssex IN('男','女')), /* CHECK定义性别只有男女 */ PRIMARY KEY (Sno) );

违约处理：拒绝操作

3.2 元组上的约束条件

与属性上约束条件的定义类似，但与属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

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.%')/* 设置元组约束 */ )

4、完整性约束命名子句

完整性约束命名子句是指使用CONSTRAINT，来对完整性约束条件命名，从而可以灵活地增加、删除后一个完整性约束条件

4.1 完整性约束命名子句

CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 9000 AND 100000), Sname CHAR(20) CONSTRAINT C2 NOT NULL, /* 使用CONSTRAINT语句给Sname字段添加非空约束，并命名为C2 */ Sage NUMERIC(3) CONSTRAINT C3 CHECK(Sage<30), Ssex CHAR(2) CONSTRAINT C4 CHECK(Ssex IN('男','女'))， CONSTRAINT StudentKey PRIMARY KEY(Sno) )

4.2 修改表中的完整性限制

使用ALTER TABLE语句修改表中的2完整性限制

去掉Student表中对性别的限制

ALTER TABLE Student DROP CONSTRAINT C4;

修改表中的约束条件，需先删除原来的约束条件，再增加新的约束条件

ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK(Sno BETWEEN 1 AND 10000)

5、域中的完整性限制建立域，添加限制并命名

CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK(VALUE IN('男','女'));

删除域中限制

ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;

增加域中限制

ALTER DOMAIN GenderDomain ADD CONSTRAINT GDD CHEK(VALUE IN('1','0'));

6、断言

断言用来指定更具一般性的约束，可定义涉及多个表或操作的比较复杂的完整性约束。

6.1 创建断言
限制数据库课程最多为60名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM CHECK (60>=(SELECT COUNT(*) FROM COURSE,SC WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库') ); /* 限制数据库课程最多为60名学生选修 */

若选修数据库课程的人数超过60人，CHECK子句返回值为“假”，对SC表的插入操作被拒绝。
6.2 删除断言

DROP ASSERTION ASSE_SC_DB_NUM

7、触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程。通过触发器，可实现在达到一定条件是对基本表进行增删改查等基本操作。

7.1 定义触发器
事件-条件-动作

触发器只能由表的拥有者创建，且触发器在一个表上有数量限制。
触发器名可以包含模式名，同一模式下，触发器名必须是唯一的。
触发器只能定义在基本表上。
触发事件可以是INSERT,DELETE或UPDATE，触发时机为ALTER/BEFORE。
触发器分为行级触发器(FOR EACH ROW)和语句级触发器(FOR EACH STATEMENT)

创建触发器举例
【数据库学习|数据库系统概论学习笔记——数据完整性】当对表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)