数据库——完整性，

文章由LinuxBoy分享于2019-06-11 09:06:14热评（14）

数据库——完整性，

一、完整性概述

1.1约束分类

1.2 DBMS对完整性的支持

1.3完整性和安全性区别

1.4完整性约束命名

二、实体完整性

2.1 SQL中的实体完整性

2.2检查和违约处理

三、参照完整性

3.1违反参照完整性的更新

3.2 SQL中的参照完整性

四、用户定义的完整性

4.1域约束

4.2断言

五、触发器

5.1定义触发器

5.2激活触发器

5.3删除触发器

一、完整性概述

数据库的完整性是指数据库中数据的正确性、一致性、相容性

正确性：保证进入数据库的数据是符合语义约束的合法数据
一致性：保证数据之间的逻辑关系是正确的，对数据库更新时，数据库从一个一致状态到另一个一致状态
相容性：同一个事实的两个数据应当是一致的

1.1约束分类

完整性约束分类

约束类型	含义
类型/域约束	说明给定类型的合法取值	用户自定义的完整性
属性约束	说明属性的合法取值	用户自定义的完整性
关系约束	说明关系的合法取值	实体完整性/用户自定义的完整性
数据库约束	说明数据库的合法取值，通常涉及多个关系	参照完整性/用户自定义的完整性

从约束的状态的角度考虑

静态约束：关于数据库正确状态的约束
动态约束：数据库从一种正确状态转移到另一种状态的转移约束

1.2 DBMS对完整性的支持

提供定义完整性约束条件的机制

提供完整性检查的方法

违约处理

1.3完整性和安全性区别

数据的完整性

数据的安全性

1.4完整性约束命名

constraint <完整性约束条件名><完整性约束条件>
--<完整性约束条件>包括not null、unique、primary key短语、foreign key短语、check短语等

--建立教师表TEACHER，要求每个教师的应发工资不低于3000元（应发工资是工资列Sal与扣除项Deduct之和）
create table TEACHER(
    Eno numeric(4) primary key,  /*在列级定义主码*/
    Ename char(10),
    Job char(8),
    Sal numeric(7,2),
    Deduct numeric(7,2),
    Deptno numeric(2),
    constraint TEACHERFKey foreign key (Deptno) references dept(Deptno),
    constraint c1 check (Sal + Deduct >= 3000)
    );

--使用 alter table语句修改表中的完整性限制

--修改表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);

二、实体完整性

实体完整性规则：关系 R的所有元组在主码上的值必须唯一，且主码上的任何属性不能为空值

删除操作不会破坏实体完整性，插入新元组和修改某个(些)元组的主码可能破坏实体完整性

SQL支持实体完整性，用户只需在创建基本表时说明关系的主码，系统就能自动地保证实体完整性

2.1 SQL中的实体完整性

单属性构成的码有两种说明方法

定义为列级约束条件
定义为表级约束条件

多个属性构成的码只有一种说明方法

定义为表级约束条件

--将Student表中的Sno属性定义为码，primary key表示主码
--在列级定义主码
create table Student(
    Sno char(9) primary key,
    Sname  char(20) not null,
    Ssex  char(2),
    Sage  int,
    Sdept  char(20)
    );
--在表级定义主码
    create table Student(
    Sno char(9),
    Sname char(20) not null,
    Ssex char(2),
    Sage int,
    Sdept char(20),
    primary key (Sno)
    );

--将SC表中的Sno，Cno属性组定义为码
create table SC(
    Sno char(9) not null,
    Cno char(4) not null,
    Grade int,
    primary key (Sno,Cno)  /*只能在表级定义主码*/
    );

2.2检查和违约处理

插入或对主码列进行更新操作时要

检查主码值是否唯一，如果不唯一则拒绝插入或修改
检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

检查记录中主码值是否唯一时，为避免对基本表进行全表扫描，RDBMS核心一般都在主码上自动建立一个索引

B+树索引

新插入记录的主码值是25

三、参照完整性

参照完整性规则：如果属性集 FK是关系 R的外码，且参照关系 S的主码 Ks，则 R的任何元组在 FK上的值或者等于 S的某个元组在主码 Ks上的值，或者为空值

如果关系数据库模式由 E-R图转换得到，则由联系集转换得到的每一个关系都存在参照完整性约束

3.1违反参照完整性的更新

违反参照完整性的情况及违约处理

参照关系 R	被参照关系 S	违约处理
插入元组	可能会违反参照完整性	拒绝
修改外码值 FK	可能会违反参照完整性	拒绝
可能会违反参照完整性	删除元组	拒绝/级联删除/置空值/置缺省值
可能会违反参照完整性	修改主码值 Ks	拒绝/级联删除/置空值/置缺省值

更新破坏参照完整性的可能措施

拒绝：不允许该操作执行。该策略一般设置为默认策略
级联：进行更新，且对更新导致违反参照完整性的参照关系元组进行相应更新
置空值：进行更新，且对更新导致违反参照完整性的参照关系元组的外码置空值。仅当外码允许取空值时才能使用
置缺省值：进行更新，且对更新导致违反参照完整性的参照关系元组的外码置空值；其中缺省值必须是被参照关系某元组主码上的值

3.2 SQL中的参照完整性

foreign key短语定义哪些列为外码，references短语指明这些外码参照哪些表的主码

foreign key (A1,...,An) references <外表名> (<外表主码>)
[<参照触发动作>]
--指出修改和删除违反参照完整性约束时触发的动作；缺省时，违反参照完整性的修改和删除将被拒绝
--参照触发动作可以是以下两种
on update <参照动作> [on delete <参照动作>]
on delete <参照动作> [on update <参照动作>]
--参照动作可以是拒绝、级联、置空值、置缺省值之一

--关系SC中（Sno，Cno）是主码。Sno，Cno分别参照Student表的主码和Course表的主码定义SC中的参照完整性
create table SC(
    Sno char(9) not null,
    Cno char(4) not null,
    Grade int,
    primary key (Sno, Cno),  /*在表级定义实体完整性*/
    foreign key (Sno) references Student(SNO),  /*在表级定义参照完整性*/
    foreign key (Cno) references Course(Cno)    /*在表级定义参照完整性*/
    );

四、用户定义的完整性

4.1域约束

每个属性都必须在一个值域上取值

在原理上类似于编程语言中变量的类型，就像不同变量可以有相同的数据类型，不同的属性可以有相同的域

声明一个域包括：域值类型，缺省值，域值的格式，对取值范围或取值集合的约束

不同域上的值不能比较

--创建域
create domain <域名> [as] <数据类型>
[default <缺省值>]
[<域约束>,...,<域约束>]

--修改域
alter domain <域名> <修改动作>
/*
修改动作
    set default <缺省值>：设置缺省值
    drop default：删除缺省值
    add <域约束>：添加域约束，其中<域约束>与 create domain相同
    drop constraint <约束名>：删除<约束名>命名的域约束
*/

--删除域
drop domain <域名> {cascade | restrict}

4.2断言

断言是一种命名约束，表达了数据库状态必须满足的逻辑条件

--创建断言
create assretion <断言名>
check (<条件>) [<约束性质>]
--每个断言都被赋予一个名字，<check 子句>中的约束条件与 where子句的条件表达式类似

--限制数据库课程最多60名学生选修
create assertion ASSE_SC_DB_NUM
check ( 60 >= (
        select count(*)
        /*此断言的谓词涉及聚集操作count的SQL语句*/
        from Course,SC
        where SC.Cno=Course.Cno and Course.Cname ='数据库'
        ));

--删除断言
drop assertion <断言名>

五、触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊类型的存储过程

指明什么事件发生和满足什么条件执行触发器
指明触发器执行什么样的动作
存储在数据库系统中
任何用户对表的增、删、改操作均由服务器自动激活相应的触发器

5.1定义触发器

create trigger <触发器名>
    {before | after} <触发事件> on <表名>
    referencing new|old row as<变量>
    for each  {row | statement}
    [when <触发条件>]<触发动作体>

/*
触发器又叫做事件-条件-动作规则
当特定的系统事件发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，通常是一段SQL存储过程
*/

语法说明

表的拥有者才可以在表上创建触发器
触发器名

表名

触发事件

AFTER表示在触发事件的操作执行之后激活触发器

BEFORE表示在触发事件的操作执行之前激活触发器

触发器类型

触发条件

触发动作体

5.2激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行

一个数据表上可能定义了多个触发器，遵循如下的执行顺序

5.3删除触发器

drop trigger <触发器名> on <表名>

推荐文章：

数据库——完整性，