数据库的完整性(5)

• 实体完整性

定义完整性在CREAT TABLE中使用PRIMARY KEY定义

一种为定义为列级约束条件

CREATE TABLE Student
		(Sno CHAR(9)PRIMARY KEY, //定义Sno为主码
		 Sname CHAR(20) NOT NULL,
		 Ssex CHAR(2),
		 Sage SMALLINT,
		 Sdept CHAR(20)
		);

一种为定义为表级约束条件(多个属性构成的码则只能使用表级约束条件)

CREATE TABLE Student
		(Sno CHAR(9), 
		 Sname CHAR(20) NOT NULL,
		 Ssex CHAR(2),
		 Sage SMALLINT,
		 Sdept CHAR(20), 
		 PRIMARY KEY(Sno) //定义Sno为主码
		);

实体完整性检查和违约处理
检查的内容包括两点：检查的主码值是否为唯一；检查主码的各个属性是否为空
全表扫描会非常的费事，所以在数据库管理系统一般都会在主码上建一个索引（B+树索引）

• 参照完整性

参照完整性在CREATE TABLE中FOREING 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), //定义Sno为Student中Sno的外码
		 FOREIGN KEY (Cno) REFERENCES Course(Cno), //定义Cno为Course中Cno的外码
		 )

参照完整系检查和违约处理
四种情况： SC表添加或是修改一个元组，Student表删除或是修改一个元组
三种修改： 拒绝执行（NO ACTION），级联（CASCADE）操作（删除相关的元组），设置为空值

CREATE TABLE SC
( Sno CHAR(9),
  Cno CHAR(4),
  Grade SMALLINT,
  FOREIGN KEY (Sno) REFERENCES Student(Sno)  //定义Sno为Student中Sno的外码
  		ON DELETE CASCADE //删除Student表中的元组时，级联删除SC表中对应的元组
  		ON UPDATE CASCADE //更新Student表中的Sno时，级联更新SC表的相应的元组
  FOREIGN KEY (Cno) REFERENCES Course(Cno), //定义Cno为Course中Cno的外码
		ON DELETE NO ACTION //删除Course表中的元组造成与SC表中不一致时，拒绝更新
		ON UPDATE CASCADE  //更新时则级联更新

)

• 用户定义的完整性

属性上约束条件

属性上约束条件的定义：列值非空（NO　NULL)；列值唯一（UNIQUE）；检查列值是否满足一个条件表达式（CHECK）
如果不满足会拒绝操作

CREATE TABLE Student
	(Sno CHAR(9)PRIMARY KEY,
	 Sname CHAR(8) UNIQUE NOT NULL, //要求Sname列值唯一，且不能取空值
	 Ssex CHAR(2)CHECK(Ssex IN ('男','女')), //只允许为“男”或者“女”
	);

元组上的约束条件

在CREATE TABLE 语句中可以用CHECK语句定义，元组级的限制可以设置不同属性之间的取值的相互约束条件。

CREATE TABLE Sdutend
	( 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.%')
	);//性别是女性或是开头不是Ms.的直接通过

• 完整性约束命名子句

完整性约束命名子句

用CONSTRAINT <完整性约束条件名> <完整性约束条件>
<完整性约束条件> 包括：NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK短语等。

CREATE TABLE TEACHER
	(Eno NUMERIC(4) PRIMARY KEY,
	 Ename CHAR(10),
	 Jon CHAR(8),
	 Sal NUMERIC（7,2), //工资
	 Deduct NUMERIC（7,2),
	 Deptno NUMERIC（2), //扣除项
	 CONSTRAINT TEACHERFKey FOREIGN KEY(Deptno)
	 REFERENCES DEPT(Deptno),  //设置Deptno为DEPT中DEPTNO的外码
	 CONSTRAINT C1 CHECK (Sal+Deduct >= 3000) //定义每一个教师的应发工资不低于3000
	);

修改表中的完整性限制

使用ALTER TABLE 语句修改完整性限制

ALTER TABLE Student
	DROP CONSTRAINT C4;
ALTER TABLE Student
	ADD CONSTRAINT CI CHECK(Sno BETWEEN 1 AND 100)

• 域中的完整性限制

建立一个域来限制取值范围CREATE DOMAIN

//建立一个域来限制性别的取值
CREATE DOMAIN GenderDomain CHAR(2)
CONSTRAINT GD CHECK (VALUE IN(男'，'女));

//删除域
ALTER DOMAIN GenderDomain
DROP CONSTRAINT GD;

//就把性别的取值范围由(男'，女)改为('1', '0')
ALTER DOMAIN GenderDomain
ADD CONSTRAINT GDD CHECK (VALUEIN(', '0));

• 断言

用CREATE ASSERTION语句>>，会触发数据库对断言的检查，任何断言不为真时都会被拒绝操作

CREATE ASSERTION ASSE_SC_DB_NUM
	CHECK (60 >= (SELECT count(*)
			FROM Course,SC
			WHERE SC.Cno =Course.Cno AND Course.Cname ='数据库')
			)；
			//当插入一个元组时断言被触发检查，如果超过60人则拒绝检查

• 触发器

触发器的定义
1、只有表的拥有者，即创建表的用户可以在表上创建触发器，且触发器的数量是限制的。
2、触发器名，触发器名必须是唯一的，且与表名在同一模式下面。
3、表名，触发器只能定义在基本表上。
4、触发事件，指明在修改哪些列时激活触发器。AFTER（操作执行后），BEFORE（操作执行前）
5、触发器的类型， 行级触发器（FOR EACH ROW），语句触发器(FOR EACH STATEMENT)
6、触发条件，触发条件为真时执行，如果省略WHEN条件，则在激活后立即执行。
7、触发动作体

CREATE TRIGGER SC_ _T   //SC_ T是触发器的名字
AFTER UPDATE OF Grade ON SC
/*UPDATE OF Grade ON SC是触发事件，*/
/*AFTER是触发的时机，表示当对SC的Grade属性修改完后再触发下面的规则*/
REFERENCING
	OLDROW AS OldTuple,
	NEWROW AS NewTuple 
FOR EACH ROW
/*行级触发器，即每执行一-次Grade的更新，下面的规则就执行一次*/
WHEN (NewTuple.Grade >= 1.1 * OldTuple.Grade) /*触发条件， 只有该条件为真时才执行*/
	INSERT INTO SC_ _U (Sno,Cno,OldGrade,NewGrade)
	/*下面的insert操作*/
	VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

激活触发器：执行表上的BEFORE触发器，激活触发器的SQL语句，执行该表上的AFTER触发器

删除触发器，DROP TRIGGER<触发器名> ON<表名>；