数据库的完整性是指数据的正确性和相容性
实体完整性
1.1定义实体完整性
关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义,一种是定义为列级约束条件,另一种是定义为表级约束条件
例:将Student表中的Sno属性定义为码
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /*列级定义*/
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
);
或
CREATE TABLE Student
(Sno CHAR(9),
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
PRIMARY KEY(Sno) /* 表级定义*/
);
例:将SC表中的Sno,Cno属性组定义为码
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno) /*只能在表级定义主码*/
);
1.2实体完整性检查和违约处理
实体完整性规则自动进行检查
1.检查主码值是否唯一,如果不唯一则拒绝插入或修改
2. 检查主码的各个属性是否为空,只要有一个为空就拒绝插入或修改
检查记录中的主码值是否唯一的一种方法是进行全表扫描,依次判断表中的每一条记录的主码值与将插入记录的主码值
参照完整性
关系模型的参照完整性在CREATE TABLE 中用FOREIGN KEY短语定义哪些列为外码,用PEFERENCES短语指明这些外码参照哪些表的主码
例:关系SC中一个元组表示一个学生选修的某门课程的成绩(Sno,Cno)是主码, Sno,Cno分别参照引用Student表的主码和Course表的主码
定义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 Student(Cno)
);
2.2 参照完整性检查和违约处理
对被参照表和参照表进行修改操作时有可能破坏参照完整性
当发生错误时,如:
SC增加一个元组,该元组的Sno属性值在表Student中找不到一个元组,其Sno属性值与之相等
系统发生这类错误时,采用以下策略
1,拒绝(NO ACTION) 执行
2,级联(CASCADE) 操作
3, 设置为空值
一般的,当对参照表和被参照表的操作违反了参照完整性,系统选用默认策略,如果想让系统采用其他策略则必须在创建参照表时显示的加以说明
例:
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(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
);
3 用户定义完整性
1,属性上约束条件的定义
属性值限制,包括:
1列值非空 (NOT NULL)
2列值唯一( UNIQUE)
3检查列值是否满足一个条件的表达式(CHECK)
(1) 不允许空值
例: 在定义SC表时,说明Sno,Cno,Grade属性 不允许取空值
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT NOT NULL,
PRIMARY KEY(Sno,Cno),
);
(2) 列值唯一
例:建立部门表DEPT,要求部门名称Dname列取唯一,部门编号Deptno列为主码
CREATE TABLE DEPT
(Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL,/*要求列值唯一*/
Location CHAR(10),
PRIMARY KEY(Deptno)
);
(3) 用CHECK短语指定列值应该满足的条件
例:Student表的Ssex只允许取‘男’或‘女’
CREATE TABLE Student
( Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK(Ssex IN('男','女')),
Sage SMALLINT,
Sdept CHAR(20)
);
2,元组上的约束条件
1,元组上的约束条件的定义
与属性上约束条件的定义类似,在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.%')
);
4 完整性约束命名子句
SQL还在CREATE TABLE 语句中提供完整性约束命名子句CONSTRAINT,用来对完整性约束条件命名
1.完整性约束命名子句
CONSTRAINT<完整性约束条件名><完整性约束条件>
<完整性约束条件>包括NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY, CHECK短语等
例:建立学生登记表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)
);
2,修改表中的完整性限制
可以使用ALTER TABLE 语句修改表中的完整性限制
例:去掉上述Student表中对性别的限制
ALTER TABLE Student
DROP CONSTRAINT C4;
例: 修改表Student中的约束条件,在增加新的约束条件。
ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999);
5 断言
通过CREATE ASSERTION语句,通过声明性断言来指定更具有一般性的约束
1创建断言语句格式
CREATE ASSERTION<断言名><CHECK 子句>
例:限制数据库课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK(60>=(SELECT count(*)
FROM Course,SC
WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库')
);
例:限制每个学期每一门课程最多60名学生选修
首先修改SC表的模式,增加一个学期"学期(TERM)"的属性
ALTER TABLE SC ADD TERM DATE;
然后定义断言
CREATE ASSERTION ASSE_SC_CNUM2
CHECK(60>=ALL(select count(*) from SC group by cno,TERM));
2,删除断言格式
DROP ASSERTION<断言名>;
总结:本次就是对第五章数据完整性的总结,知识点较多,要多去记忆