数据库的完整性
数据库的完整性
数据库的完整性：指数据的正确性和相容性。正确性是指数据的合法性、数据是否属于所定义域的有效范围。相容性是指表示数据库同一对象在不同关系表中的数据符合逻辑。
为维护数据库的完整性，DBMS必须能够：
- 提供定义完整性约束条件的机制，一般由SQL的DLL语句来实现，作为数据库模式的一部分存入数据字典中。
- 提供完整性检查的方法，在insert、update、delete语句执行完后开始检查
- 违约处理，当违背完整性约束条件时，采取的动作（拒绝或级联）
5.1实体完整性
插入或对主码列进行更新操作时，RDBMS（关系数据库管理系统）按照实体完整性规则自动进行检查。包括：
- 检查主码值是否唯一，如果不唯一则拒绝插入或修改
- 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改
比如，插入主键值完全相同的两行数据，系统会拒绝插入；插入主键的某个属性为空的数据，系统会拒绝插入；修改也是一样
实体完整性检查和违约处理：
- 检查记录中主码值是否唯一的一种方法是进行全表扫描（非常耗时）
- RDMBS（优化关系数据库管理系统）一般会在主码上自动建立一个索引，例如B+数索引，通过索引查找基本表是否已经存在新的主码值将大大提高效率。
将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属性组定义为码
将SC表中的Sno,Cno属性定义为码
CREATE TABLE SC(
Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno) ); /*只能在表级定义主码*/
5.2参照完整性
对参照表和被参照表的更新操作都可能破坏参照完整性
参照完整性违约处理：
拒绝(NO ACTION)执行（默认策略）
级联(CASCADE)操作
设置为空值(SET NULL)
对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值
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)
ON DELETE CASCADE/*级联删除SC表中相应的元组*/
ON UPDATE CASCADE/*级联更新SC表中相应的元组*/
说明如下:
这样定义sno为外键，参照student的sno ，并且说明当student中的sno被修改或删除时，那么违约处理的策略为:级联修改或删除，即 sc表中参照它的sno也会被修改或删除。
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION/*拒绝删除*/
ON UPDATE CASCADE/*级联更新SC表中相应的元组*/
说明如下:
这样定义cno为外键，参照course 的cno ,并且说明当course中的cno被删除时，违约处理的策略是拒绝( noaction) ;当course 中的cno被修改时，违约处理的策略是SC表中参照这个cno的cno被级联( cascade )修改
5.3用户定义的完整性
用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求
由RDBMS提供,而不必由应用程序承担，即如果在数据库中定义了用户自定义完整性约束，则完整性检查有RDBMS负责,程序中不需要做检查,减轻了程序的负担
注意:实际情况是，完整性检查还是由程序完成，不需要将此任务交给DBMS,为了提高效率
5.3.1 属性上的约束条件的定义
通常在CREATE TABLE时在列级定义。
列值唯一(UNIQUE短语)
建立部门表 DEPT,要求部门名称 Dname 列取值唯一部门编号Deptno 列为主码
CREATE TABLE DEPT(
Deptno CHAR(2),
Dname CHAR(9) UNIQUE,
Location CHAR(10),
PRIMARY KEY (Deptno));
自动增长
创建订单表order(oid,cid,cname,orderdate)其中 oid是订单号, cid是客户编号, orderdate是下单日期，设置oid是主键并自动增长
create table orderList(
oid int AUTO_INCREMENT,
cid char(10) not null,
cname char(20),
orderdate date,
PRIMARY key (oid));
不允许取空值(NOT NULL短语)
在定义SC表时，说明Sno，Cno不允许取空值
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno，Cno) ,);
对已经建立好的选修表SC，为其grade列设置不允许取空值
alter tab1e sc modify grade float not nu11;
检查列值是否满足一个布尔表达式(CHECK短语)
注：MySQL不支持check子句
Student 表的Ssex只允许取“男”或“女”，年龄介于到100岁之间
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK (Ssex IN(’男'，‘女'))，
Sage SMALLINT CHECK(Sage >= 0 AND Sage <= 100) Sdent. CHAR (20));
列值缺省(DEFAULT短语)
建立学生表Student ,要求性别Ssex缺省值为‘男’，学号Sno列为主码
CREATE TABLE Student(
Sno CHAR(9),
SName CHAR(20) NOT NULL,
Ssex CHAR() DEFAULT ‘男',
Sage SMALLINT,
Sdept CHAR(20)，PRIMARY KEY (Sno));
建立学生表Student时，为性别Ssex列设置了缺省值为‘男’后，执行下面的语句:
insert into student(sno , sname) values ( ' 001', 'zhangsan') ;
后，实际被执行的插入语句为:
insert into student(sno, sname , ssex) values( ' 0o1' , ' zhangsan',’男')﹔
ssex被设置了默认值男。
插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足。如果不满足则操作被拒绝执行。例如在sname 上设置了非空约束，则在插入数据时没有给sname 指定值则 DBMS会拒绝插入。
5.3.2 元组上的约束条件的定义
元组级上的约束，就是指在表级上多个列上的约束在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.%'));
5.3.3完整性约束命名子句
SQL还在CREATE TABLE 语句中提供完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名
完整性约束命名子句
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));
修改表中的完整性限制
可以使用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.3.4 断言
通过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<断言名>;
