数据库系统概论 第五章 数据库完整性

1.实体完整性

1.1 实体完整性定义

  关系模型的实体完整性再CREATE TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件。
【例】

/*将Student表中的Sno属性定义为码。*/
CREATE TABLE Student(
	Sno CHAR(8) RIMARY KEY, /*在列级定义主码*/
	Sname CHAR(20) NOT NULL,
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)
	/*或在表级定义主码*/
	/*PRIMARY KEY(Sno)*/
	);

/*将SC表中的Sno,Cno属性组定义为码*/
CREATE TABLE SC(
	Sno CHAR(8) NOT NULL,
	Cno CHAR(4) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno)	/*只能在表级定义主码*/
	);

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

  用PRIMARY KEY短语定义了关系的主码后，没当用户程序对基本表插入一条记录或者对主码列进行更新操作时，RDBMS将对数据进行实体完整性检查，包括：

• 1.检查主码值是否唯一，如果不唯一则拒绝插入或修改。
• 2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

---

2.参照完整性

2.1 参照完整性定义

  关系模型的参照完整系在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指定这些外码参照哪些表的主码。
【例】

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 参照完整性检查和违约处理

  对被参照表和参照表进行增删改操作时有可能破坏参照完整新，必须进行检查。
  例如，对表SC和Student有四种可能破坏参照完整性的情况。

• 1.SC表中增加一个元组，该元组的Sno属性在Student中找不到一个具有相同Sno属性值的元组。
• 2.修改SC表中的一个元组，修改后该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性值与之相等。
• 3.从Student表中删除一个元组，造成SC表中某些元组的Sno属性的值在表Student中找不到具有相同Sno属性值的一个元组。
• 4.修改Student表中一个元组的Sno属性，造成SC表中某些元组的Sno属性的值在表Student中找不到具有相同Sno属性值的一个元组。

---

  当上诉的不一致发生时，系统可以采用以下的策略进行处理。

• 1.拒绝（NO ACTION）执行
  不允许该操作执行。该策略一般设置为默认策略。
• 2.级联（CASCADE）操作
  当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组。
• 3.设置为空值（SET-NULL）
  当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。
  比如下面有两个关系
  $学生（\underline{学号}，姓名，性别，专业号，年龄）$
  $专业（\underline{专业号}，专业名）$
  学生关系的“专业号”是外码，因为专业号是专业关系的主码。
  假设专业表中某个元组被删除，专业号为12，按照设置为空值的策略，就要把学生表中专业号=12的所有元组的专业号设置为空值。

---

3.用户定义的完整性

3.1 属性（列）上的约束条件的定义

  在CREATE TABLE中定义属性的同时可以根据要求，定义属性上约束条件，即属性值限制，包括

• 列值非空（NOT NULL）
• 列值唯一（UNIQUE）
• 检查列值是否满足一个布尔表达式（CHECK）

【例】

/*UNIQUE*/
CREATE TABLE DEPT(
	Deptno NUMERIC(2),
	Dname CHAR(9) UNIQUE, /*要求Dname列值唯一*/
	Location CHAR(10),
	PRIMARY KEY(Deptno)
	);
/*NOT NULL AND  CHECK*/
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 Student(Sno),
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
	);

3.2 属性（列）上的约束条件检查和违约处理

  当往表中插入元组或修改属性的值时，RDBMS就检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。

3.3 元组上的约束条件的定义

  与属性上约束条件的定义类似，在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制。同属性值（列）限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。
【例】 当学生的性别是男时，其名字不能以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.%')
	);

3.4 元组上的约束条件检查和违约处理

  当往表中插入元组或修改属性的值时，RDBMS就检查元组上的约束条件是否满足，如果不满足则操作被拒绝执行。

---

4.完整性约束命名子句

  SQL还在CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件。

4.1完整性约束命名子句

格式如下

CONSTRAINT <完整性约束条件名> [PRIMARY KEY 短语|FOREIGN KEY 短语]

【例】 建立学生登记表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)
);

---

4.2 修改表中的完整性限制

  我们可以使用ALTER TABLE语句修改表中的完整性限制。
【例】

/*去掉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 900000 AND 999999);
ALTER TABLE Student
	DROP CONSTRAINT C3;
ALTER TABLE Student
	ADD CONSTRAINT C3 CHECK(Sage < 40);

---

5.域中的完整性限制

  SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该域应该满足的完整性约束条件。

//1.建立一个性别域，并声明性别域的取值范围
CREATE DOMAIN GenderDomain CHAR(2)
	CHECK (VALUE IN ('男','女'));
/*这样在创建表中的说明可以写为
Ssex GenderDomain*/
//2.建立一个性别域GenderDomain，并对其中的限制命名。
CREATE DOMAIN GenderDomain CHAR(2)
	CONSTRAINT GD CHECK (VALUE IN ('男','女'));
//3.删除域GenderDomain的限制条件GD.
ALTER DOMAIN GenderDomain
	DROP CONSTRAINT GD;
//4.在域GenderDomain上增加限制条件GDD。
ALTER DOMAIN GenderDomain
	ADD CONSTRAINT GDD CHECK (VALUE IN ('1','0'));

---

6.触发器

  触发器（Trigger） 是用户定义在关系表上的一类由事件驱动的特殊过程，一旦定义，任何用户对表的增、删、改操作均由服务器自动激活响应的触发器。

6.1 INSERTED表和DELETED表

  在SQL SERVER 2008中，DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中。我们只有只读的权限。当触发器执行完成后，它们也会被自动删除。

• INSERTED表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 INSERTED表和触发器表中。INSERTED表中的行是触发器表中新行的副本。
• DELETED表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 DELETED表中。DELETED 表和触发器表通常没有相同的行。

6.2 定义触发器

  SQL中使用CREATE TRIGGER建立触发器，格式如下：

CREATE TRIGGER <触发器名>
ON <表名>
FOR|AFTER|INSTEAD OF
[DELETE,INSERT,UPDATE]
AS
	SQL语句

【例】 在S表创建UPDATE触发器

CREATE TRIGGER tri_Updates
ON s
FOR UPDATE
AS
PRINT "THE TABLE S WAS UPADATED";

【例】 禁止删除SC表中成绩不及格学生的记录

CREATE TRIGGER tri_del_grade
ON SC 
FOR DELETE
AS 
	IF EXISTS(SELECT * FROM DELETED
	WHERE Grade<60)
	ROLLBACK

【例】 禁止将SC表中不及格学生的成绩改为几个

CREATE TRIGGER tri_update_grade
ON SC 
FOR UPDATE
AS
	IF UPDATE(grade)
	IF EXISTS(SELECT * FROM INSERTED,DELETED
	WHERE INSERTED.Sno = DELETED.Sno
	AND INSERTED.Grade>=60
	AND DELETED.Grade<60)
	BEGIN 
	PRINT "不能将不及格的成绩改为及格"
	ROLLBACK
	END

6.3 删除触发器

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

6.4 修改触发器

ALTER TRIGGER <触发器名>
ON <表名>
FOR|AFTER|INSTEAD OF
[DELETE,INSERT,UPDATE]
AS
	SQL语句