第12讲：数据库完整性

佚名 5年前 (2019-04-21) 随笔 1554人围观抢沙发百度已收录

大纲：

数据库完整性的概念及分类
SQL语言实现静态(列/关系)完整性
SQL语言实现动态完整性

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

一、前言

1. 数据库完整性是数据库的一种特性(在任何情况下的正确性、有效性和一致性)，其由DBMS保证实现

①广义完整性：语义完整性、并发控制、安全控制、故障恢复

②狭义完整性：特指语义完整性，DBMS通常有专门的完整性管理机制与程序来处理语义完整性问题

2. 回顾关系模型中的三个完整性：实体完整性、参照完整性、用户自定义完整性

用户自定义完整性即是上述语义完整性，而这也是我们这讲的主题。

3. 本讲目的：学会把完整性约束条件施加到数据库的定义中，学会如何表达完整性约束条件。

二、数据库完整性概述

1. 为什么会引发数据库完整性的问题？

不正当的数据库操作，如输入错误、操作失误、程序处理失误等

2. 数据库完整性管理的作用：

防止和避免数据库中不合理数据的出现
DBMS应尽可能地自动防止DB中的语义不合理现象

3. DBMS如何自动保证数据库完整性？

DBA定义完整性约束规则；
当用户请求更新数据时，DBMS将依据完整性约束规则来检查此更新是否符合规则，进而决定是否允许更新。

三、数据库完整性的分类

1. 按约束对象分类

域完整性约束条件：施加于某一列上，对给定列上所要更新的某一候选值是否可以接受进行约束条件判断
关系完整性约束条件：施加于关系(若干列)上，对给定关系上所要更新的某一候选元组是否可以接受进行约束条件判断；或是对一个关系中的若干元组和另一个关系中的若干元组间的联系是否可以接受进行约束条件判断

2. 按约束来源分类

结构约束：来自于模型的约束，例如函数依赖约束、主键约束(实体完整性)、外键约束(参照完整性)，只关心数值相等与否，是否允许空值等
内容约束：来自于用户的约束，如用户自定义完整性，关心元组或属性的取值范围

3. 按约束状态分类

静态约束：要求DB在任何时候均应满足的约束，如Sage在任何时候均应满足“大于0且小于150”
动态约束：要求DB从一状态变为另一状态时应满足的约束，例如人的身高只能增加，不能减少(不可从170更改为165)

四、完整性约束条件(或完整性约束规则)的表达

1. 完整性约束条件的一般形式：(O, P, A, R)

O：数据集合，约束的对象，可以是列、多列(元组)、元组集合
P：谓词条件表达式，约束是什么
A：触发条件，DBMS什么时候检查P条件是否满足
R：响应动作，当条件不满足时应采取的动作

五、用SQL语言实现静态完整性

1. 静态约束

①列完整性：域完整性约束

②表完整性：关系完整性约束

③O(列或表)、P(根据需求而定义)、A(更新时检查<默认>)、R(拒绝<默认>)

2. create table具有定义完整性约束(包括列完整性和表完整性)这一功能，形式如下：

create table 表名

((列名数据类型 [default {默认值 | null}] // 默认值，当用户不输入时填充该值

[列约束 [列约束 ... ]] // 多个列约束之间以空格分隔

| , 表约束 // 表约束前必须用逗号加以区分

[, [列名数据类型 [default {默认值 | null}]

[列约束 [列约束 ... ]]

| , 表约束] ... ]);

3. 列约束只能对单一列的值进行约束，其语法形式如下：

{not null |　　　　　　　　　　　　　　　　　 // 列值不允许为空值

[constraint 约束名]　　　　　　　　　　　　 // 给该约束命名，便于以后撤消

{ unique |　　　　　　　　　　　　　　　 // 列值是唯一的，即候选键

primary key |　　　　　　　　　　　　　// 该列为主键

check (谓词条件) |　　　　　　　　　 // 该列在更新时应满足的该谓词条件

references 表名[(列名)] // 把该列定义为外键，说明是哪个表的主键，并指出主键的列名

[on delete {cascade | set null}] } // 另一表删除一个元组后，本表的外键所在元组应如何处理

}

【示例1】定义Student表的完整性约束，其中S#不许为空值且值唯一，Ssex只能是男或女，Sage必须大于0且小于150，D#是外键：

create table Student (S# char(8) not null unique, Sname char(10), Ssex char(2) constraint ctssex check (Ssex = '男' or Ssex = '女'), Sage integer check (Sage >= 1 and Sage < 150), D# char(2) references Dept(D#) on delete cascade, Sclass char(6));

【示例2】定义Course表的完整性约束，其中每门课的学分必须大于等于0且小于等于5，T#是外键：

create table Course (C# char(3), Cname char(12), Chours integer, Credit float(1) constraint ctcredit check (Credit >= 0.0 and Credit <= 5.0), T# char(3) references Teacher(T#) on delete cascade);

【示例3】check中的条件可以是select语句中任何where后接的条件，包含子查询：

create table SC (S# char(8) check(S# in (select S# from Student)), C# char(3) check(C# in (select C# from Course)), Score float(1) constraint ctscore check (Score >= 0.0 and Score <= 100.0));

4. 表约束可以对多列或元组的值进行约束，其语法形式如下：

[constraint 约束名]

{unique (列名 [, 列名 ... ]) | // 多个列组合在一起作为候选键

primary key (列名 [, 列名 ... ]) |　　　　　 // 多个列组合在一起作为主键

check (谓词条件) | // 多列在更新时应满足的谓词条件

foreign key (列名 [, 列名 ... ]) // 把若干列定义为外键，这些列对应另一个表的主键

references 表名[(列名 [, 列名 ... ])]

[on delete cascade]} // 另一个表删除元组时，该表也删除对应外键所在行

【示例1】令S#列为Student表的主键：

create table Student (S# char(8) not null unique, Sname char(10), Ssex char(2) constraint ctssex check (Ssex = '男' or Ssex = '女'), Sage integer check (Sage >= 1 and Sage < 150), D# char(2) references Dept(D#) on delete cascade, Sclass char(6) , primary key(S#));

【示例2】令C#列为Course表的主键，且约束一学时为20学分：

create table Course (C# char(3), Cname char(12), Chours integer, Credit float(1) constraint ctcredit check (Credit >= 0.0 and Credit <= 5.0), T# char(3) references Teacher(T#) on delete cascade , primary key(C#), constraint ctcc check (Chours / Credit = 20));

【示例3】令SC表的S#列为Student表的外键，C#列为Course表的外键：

create table SC (S# char(8) , C# char(3), Score float(1) constraint ctscore check (Score >= 0.0 and Score <= 100.0) , foreign key (S#) references Student(S#) on delete cascade, foreign key (C#) references Course(C#) on delete cascade);

5. 撤消已定义的列/表约束 & 追加或更改列/表约束

alter table 表名

[add (列名数据类型 [default {默认值 | null}] // 添加一列，同时带上该列的约束

[列约束 [列约束 ... ]] | , 表约束

{, 列名数据类型 ... })]

[drop {column 列名 | (列名 [, 列名 ...])}] // 撤消一列或多列

[modify (列名数据类型 [default {默认值 | null}] [[not] null] // 追加列的约束

{, 列名数据类型 ... })]

[add constraint 约束名]

[drop constraint 约束名]

[drop primary key];

【示例1】撤消SC表的ctscore约束：