数据库概论.陈立军.04.SQL 完整性

Posted on Edited on In Views:
约束类型、约束系统表、表级约束、约束命名、约束检查、延迟约束、函数约束、assertion

在线 SQL

  • http://sqlfiddle.com/
  • MySQL 5.6

SQL 完整性

关系模型中的完整性

实体完整性

  • 关系的主码中的属性值不能为空值
  • 实体是相互可区分的

参照完整性

  • 外码必须和某个参照的属性相同
  • 必须与客观存在的实体发生联系

用户定义的完整性

  • 用户针对具体应用环境定义的完整性约束条件
  • 例如一些具体的限定
    • sno要求是8位整数，首位是0或1
    • 飞行员的飞行里程与星级评定
    • 选课人数不能少于10人，多于100人
    • 在本地纳税记录超过5年才有购房资格
    • 婚姻登记必须购买百年好合保险

比较

  • 实体完整性和参照完整性由系统自动支持
  • 系统提供定义和检验用户定义的完整性的机制

约束类型

  • 列级约束
    • 列值范围
    • 例子
      • 取值范围
  • 行级约束
    • 同一行各列之间
    • 例子
      • 飞行员的等级和他的飞行里程有关
      • 主码约束
  • 表级约束
    • 行间、表上、表间
    • 例子
      • 外码约束
  • 查看约束：sp_helpconstraint
  • SQL
    • primarykey
    • unique
    • foreignkey
    • check
    • default

约束系统表

  • Sysconstraints 系统表
列名 数据类型 描述
constid int 约束号
id int 拥有该约束的表ID
colid smallint 在其上定义约束的列ID，如果是表约束则为0
status int 位图指示状态。可能的值包括：
1 = PRIMARY KEY 约束
2 = UNIQUE KEY 约束
3 = FOREIGN KEY 约束
4 = CHECK 约束
5 = DEFAULT 约束
16 = 列级约束
32 = 表级约束

primary key 与 unique

  • 都是通过唯一性索引来支持
  • 区别
    • primary key 不能为空值，unique 可以为空值
    • unique 列只能有一个 null
  • unique 可以定义在多个列上，此时可以出现多个 null
1
2
3
4
5
6
create table t1 (
    col1 intunique,
    col2 int,
    col3 int,
    unique(col2,col3)
)
Col1 Col2 Col3
1 3 4
2 5 null
null 6 null 
1
2
3
4
5
6
7
8
9
10
11
12
create table t1 (
    col1 int unique,
    col2 int,
    col3 int,
    unique(col2,col3)
);
insert t1 values(1,1,1);
/* insert t1 values(1,2,2);*/     /* ERROR */
insert t1 values(2,1,null);
insert t1 values(3,1,null);       /* OK */
insert t1 values(4,1,null);       /* OK */
/* insert t1 values(5,1,1);*/     /* ERROR */

primary key 与 unique 的背后

  • 系统自动生成一张约束系统表

foreign key

  • 基本关系：主码所在的关系
  • 依赖关系：外码所在的关系
  • 参照完整性
    • 当外码所在的表需要插入一个记录的时候，需要检查外码的值再主码中存在
    • 当主码所在的表删除一个记录的时候，可能也会影响参照完整性

foreign key 的三种定义方式

RESTRICT 方式

  • 只有当依赖关系中没有一个外码值与要删除（更新）的基本关系的主码值相对应时，才可以删除（更新）该行（的主码），否则系统拒绝此删除操作

CASCADE方式

  • 将依赖关系中所有外码值与基本关系中要删除的主码值所对应的行一起删除（将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值）

SET NULL方式

  • 删除（更新）基本关系中的行时，将依赖关系中与基本关系中被删（更新）主码值相对应的外码值置为空值

表级约束

  • 涉及多行或多表之间的联系
1
2
3
4
5
6
7
8
create table SC (
    sno char(8),
    cno char(10),
    grade smallint,
    primay key(sno,cno),
    check(sno in(select sno from S)),
    check(cno in(select cno from C))
)
  • 好像是在声明外码约束，有一些细节上的区别
    • check 定义在 SC 中，当修改 S 表或 C 表的时候是不会有检查的

约束命名及其定义

1
constraint 约束名<约束条件>
  • 撤销与添加约束
1
2
alter table ... drop constraint ...
alter table ... add constraint ...
  • 例子
1
2
3
4
5
6
create table S (
    sno char(8) constraint S_PK primary key
)
alter table S drop constraint S_PK
alter table SC add constraint SC_CHECK
    check(sno in(select sno from S))

约束检查

  • 相互参照的表，如何插入行

  • 同样的问题，自参照的表

  • 先 drop constraint，在插入之后再 add constraint
    • 不可行的，可能会有不满足约束条件的数据混入，不安全
  • 可以先整理成一个树形的结构，然后从根部开始插入数据

延迟约束

  • deferred constraints
    • 将多个更新操作语句放入一个事务，在提交时才检查约束
  • 如何设置延迟约束
1
2
3
4
5
/* 在约束创建时 */
[not] deferrable
initially deferred [immediate]
/* 对现有约束 */
set constraint 约束名 deferried
  • 例子
1
2
3
4
5
6
7
8
9
10
create table emp(
    eno     char(10) primary key,
    ename   char(20),
    mgr     char(10) constraint FK_Constraint   foreign key
                    references  emp(eno)
                    deferrable initially immediate /* 初始的时候立即检查 */
)

/* 在导入数据的时候,设置延迟约束 */
set constraint FK_Constraint deferried

SQL Server 的约束开关

  • with nocheck
  • 暂时关闭约束开关
1
2
3
4
5
create table t1(col_a INT)
insert into t1 values(-1)
alter table t1 with nocheck
add constraint skip_check check(col_a > 1)
insert into t1 values(-2) /* 可以执行 */
  • 禁用并重新启用一个约束(nocheck)
1
2
3
alter table t1 nocheck constraint skip_check
insert into t1 VALUES(-2)
alter table t1 check constraint skip_check
  • 上面的操作很危险，可能混入不满足约束条件的记录
  • SQL Server 提供了一个补救措施
1
2
/* 列出不满足条件的行记忆他们不满足哪一个约束条件 */
dbcc checkconstraints

函数约束

  • 约束条件非常复杂，简单的 check 无法实现

例子 1

  • 当插入一个新行的时候，使用的值是当前没有使用的最小值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/* 创建表 */
create table noGap(
    no int primary key,
    name varchar(10)
)
/* 函数约束 */
create function fn_noGap_minUnusedKey()
returns int as
begin
    /* 尚未申请则返回 1 */
    if(not exists(select * from noGap where no = 1)) return 1
    declare @min_unused_no int
    select @min_unused_no = min(no + 1)
    from noGap NO1
    where not exists(
        select *
        from noGap NO2
        where NO2.no = NO1.no + 1
    )
    return @min_unused_no
end

/* 使用约束 */

alter table noGap
      add constraint DF_no default(fn_noGap_minUnusedKey())for no

例子 2

  • 选课人数不能超过 100
1
2
3
4
5
6
7
8
9
create function fn_registerCount(@cno varchar )
returns int as
begin
    return (
        selectcount(*)
        from sc
        where cno= @cno
    )
End
1
2
alter table sc
      add constraint CK_registerCount check(fn_registerCount(cno) <= 100)

assertion

  • 实际数据库不支持
  • 检查开销太大了
1
2
3
4
5
6
7
8
9
/* AtoB 只能有一条路径 */
create assertion AtoB check (
    not exists (
        select A
        from R
        group by A
        having count(distinct B) > 1
    )
)