SQL关于数据库完整性

SQL语句关于数据库完整性

数据的正确性和相容性
5.1实体完整性
5.1.1 定义实体完整性

例5.1 将Student表中的Sno属性定义为码.
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /在列级定义主码/
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)				/*在表级定义主码*/
		 );

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

5.1.2 实体完整性检查和违约处理
5.2 参照完整性
5.2.1 定义参照完整性

例5.3 定义SC中的参照完整性．
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) /在表级定义参照完整性/
);

5.2.2 参照完整性检查和违约处理

例5.4 显式说明参照完整性的违约处理示例
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno), /在表级定义实体完整性,Sno,Cno都不能取空值/
FOREIGN KEY (Sno) REFERENCES Student(Sno) /在表级定义参照完整性/
ON DELETE CASCADE
/当删除Student表中的元组时,级联删除SC表中相应的元组/
ON UPDATE CASCADE
/当更新Student表中的sno时,级联更新SC表中相应的元组/
FOREIGN KEY(Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION
/当删除Course表中的元组造成与SC表不一致时,拒绝删除/
ON UPDATE CASCADE
/当更新Course表中的cno时,级联更新SC表中相应的元组/
);

5.3 用户定义的完整性
5.3.1 属性上的约束条件
1.属性上约束条件的定义

(1)**不允许取空值**

例5.5 在定义SC表时,说明Sno,Cno,Grade属性不允许取空值.
	CREATE TABLE SC
		(Sno CHAR(9) NOT NULL,		/*Sno属性不允许取空值*/
		Cno CHAR(4) NOT NULL,		/*Cno属性不允许取空值*/
		Grade SAMLLINT NOT NULL,	/*Grade属性不允许取空值*/
		PRIMARY KEY (Sno,Cno),		/*在表级定义实体完整性,隐含了Sno,Cno不允许取空值,
														在列级不允许取空值的定义可不写*/
						...
		);
		
(2)**列值唯一**

例5.6 建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码.
	CREATE TABLE DEPT
		(Deptno NUMBERIC(2),
		Dname CHAR(9) UNIQUE NOT NULL, /*要求Dname列值不唯一,且不能取空值*/
		Location CHAR(10),
		PRIMARY KEY (Deptno)
		);
		
(3)**用CHECK短语指定列值应该满足的条件**

例5.7 Student表的Ssex只允许取"男"或"女".
	CREATE TABLE Student
		(Sno CHAR(9) PRIMARY KEY,			/*在列级定义主码*/
		Sname CHAR(8) NOT NULL,			/*Sname属性不允许取空值*/
		Ssex CHAR(2) **CHECK (Ssex IN ('男','女')),**   /*性别属性Ssex只允许取'男'或'女'*/
		Sage SMALLINT,
		Sdept CHAR(20)
		);
		
例5.8 SC表的Grade的值应该在0和100之间
	CREATE TABLE SC
		(Sno CHAR(0),
		Cno CHAR(4),
		Grade SMALLINT **CHECK(Grade>=0 AND Grade<=100),**	/*Grade取值范围是0到100*/
		PRIMARY KEY (Sno,Cno),
		FOREIGN KEY (Sno) REFERENCES Student (Sno),
		FOREIGN KEY (Cno) REFERENCES Course(Cno)
		);

5.3.2 元组上的约束条件
1.元组上约束条件的定义

例5.9 当学生的性别是男时,其名字不能以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.%’)
); /定义了元组中Sname和Ssex两个属性值之间的约束条件/

5.4 完整性约束命名子句
1.完整性约束命名子句
CONSTRAINT <完整性约束条件名><完整性约束条件>
NOT NULL, UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK

例5.10 建立学生登记表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 NUMBERIC(3)
				 **CONSTRAINT C3 CHECK (Sage<30),**
			Ssex CHAR(2)
				**CONSTRAINT C4 CHECK (Ssex IN('男','女')),**
				**CONSTRAINT StudentKey PRIMARY KEY(Sno)**
			);

例5.11 建立教师表TEACHER,要求每个教师的应发工资不低于3000元.
应发工作是工资列Sal与扣除项Deduct之和.
CREATE TABLE TEACHER
(Eno NUMBERIC(4) PRIMARY KEY,
Ename CHAR(10),
Job CHAR(8),
Sal NUMBERIC(7,2),
Deptno NUMBERIC(2),
CONSTRAINT TEACHERKey FOREIGN KEY(Deptno)
REFERENCES DEPT (Deptno),
CONSTRAINT C1 CHECK(Sal+Deduct >=3000)
);

2.修改表中的完整性限制

例5.12. 去掉例5.10 Student表中对性别的限制.
		ALTER TABLE Student
				**DROP CONSTRAINT C4;**
				
例5.13 修改表Student中的约束条件,要求学号改为在900 000~999 999之间,年龄由小于30改为小于40.
		可先删除原来的约束条件,再增加新的约束条件.
		ALTER TABLE Student
				**DROP CONSTRAINT C1;**
		ALTER TABLE Student
				**ADD CONSTRANINT C1 CHECK (Sno BETWEEN 900 000 AND 999999);**
		ALTER TABLE Student
				**DROP CONSTRAINT C3;**
		ALTER TABLE Student
				**ADD CONSTRAINT C3 CHECK (Sage<40);**
				
*5.5 域中的完整性限制
例5.14 建立一个性别域,并声明性别域的取值范围.
		CREATE DOMAIN GenderDomain CHAR(2)
			CHECK (VALUE IN('男','女'));
			
例5.15 建立一个性别域GenderDomain,并对其中的限制命名.
		CREATE DOMAIN GenderDomain CHAR(2)
			CONSTRAINT GD CHECK (VALUE IN ('男,'女'));
			
例5.16 删除域GenderDomain的限制条件GD.
		ALTER DOMAIN GenderDomain
				DROP CONSTRAINT GD;
				
例5.17 在域 GenderDomain 上增加性别的限制条件GDD.
		ALTER DOMAIN GenderDomain
				ADD CONSTRAINT GDD CHECK (VALUE IN ('1','0'));
5.6 断言
	1.创建断言的语句格式
	**CREATE ASSERTION <断言名> <CHECK 子句>**
	
例5.18 限制数据库课程最多60名学生选修.
	CREATE ASSERTION ASSE_SC_DB_NUM
			CHECK (60>=(SELECT count(*)	/*断言的谓词设计聚集操作count的SQL语句*/
			FROM Course,SC
			WHERE SC.CNO=COURSE.CON AND COURSE.CNAME='数据库')
		);
		
例5.19 限制每一门课程最多60名学生选修.
	CREATE ASSERTION ASSE_SC_CNUM1
		CHECK(60>=ALL(SELECT count(*)			/*此断言的谓词,涉及聚集操作count*/
							FROM SC								/*和分租函数group by的SQL语句*/
							GROUP by cno)
						);
						
例5.20 限制每个学期每一门课程最多60名血汗俄国选秀.
首先修改SC表的模式,增加一个"学期(TERM)"的属性.
		ALTER TABLE SC ADD TERM DATE; /*先修改SC表,增加TERM属性,它的类型是DATE*/
然后定义断言:
		CREATE ASSERTION ASSE_SC_CNUM2
				CHECK (60>=ALL(select count(*) from SC group by cno,TERM));
				
2.删除断言的语句格式
	**DROP ASSERTION<断言名>;**

5.7 触发器
5.7.1 定义触发器
一般格式:
CREATE TRIGGER <触发器名> /每当触发事件发生时,该触发器被激活/
{BEFORE | AFTER} <触发事件> ON <表名>
/指明触发器激活的时间是在执行触发事件前或后/
REFERENCING NEW|OLD ROW AS<变量>
/REFERENCING 指出引用的变量/
FOR EACH { ROW | STATEMENT}
/定义触发器的类型,指明动作执行的频率/
[ WHEN <触发条件> ] <触发动作体>
/仅当触发条件为真时才执行触发动作体/

例5.21 当对表SC的Grade属性进行修改时,若分数增加了10%,则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade)中,其中Oldgrade是修改前的分数,Newgrade是修改后的分数.
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)

例5.22 将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中.
CREATE TRIGGER Student_Count
ALTER INSERT ON Student
/指明触发器激活的时间是在执行INSERT后/
REFERENCING
NEW TABLE AS DELTA
FOR EACH STATEMENT
/语句级触发器,即执行完INSERT语句后下面的触发动作体才执行一次/
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM DELTA

例5.23 定义一个BEFORE行级触发器,为教师表Teacher定义完整性规则"教授的工资不得低于4000元,如果低于4000元,自动改为4000元".
CREATE TRIGGER Insert_Or_Update_Sal
/对教师表插入或更新时激活触发器/
BEFORE INSERT OR UPDATE ON Teacher
/BEFORE触发事件/
REFERENCING NEW row AS newTuple
FOR EACH ROW /这是行级触发器/
BEGIN /定义触发动作体,这是一个PL/SQL过程块/
IF (newtuple.Job=‘教授’) AND (newtuple.Sal<4000)
/因为是行级触发器,可在过程体中/
THEN newtuple.Sal:=4000;
END IF;
END; /触发动作体结束/

关注和评论,是对我最大的支持,我会竭尽所能帮助需要帮助的朋友们