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语句