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

数据库完整性

1、实体完整性

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) /* 多个属性构成的主码只能有一种说明方法,即表级约束 */
    );
    
  2. 列级约束

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

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

1.2.1 检查内容:

  1. 检查主码值是否唯一,不唯一则拒绝插入或修改。

  2. 检查主码的各个属性是否为空(多属性主码),为空则不允许进行操作。

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 应对策略

  1. 拒绝(NO ACTION)执行

    拒绝执行该操作

  2. 级联(CASCADE)操作

    即破坏参照完整性后,删除或修改所有相关元组

  3. 设置为空值

例子:

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 定义触发器

事件-条件-动作

  1. 触发器只能由表的拥有者创建,且触发器在一个表上有数量限制。
  2. 触发器名可以包含模式名,同一模式下,触发器名必须是唯一的。
  3. 触发器只能定义在基本表上。
  4. 触发事件可以是INSERT,DELETE或UPDATE,触发时机为ALTER/BEFORE。
  5. 触发器分为行级触发器(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)

你可能感兴趣的