淘先锋技术网

首页 1 2 3 4 5 6 7

数据库系统概论--（五）数据库完整性

数据库得完整性是指数据的正确性和相容性。

数据的正确性指的是符合现实世界语义、反应当前实际情况的；

数据的相容性是指数据库同一对象在不同的关系表中的数据是符合逻辑的。

数据的完整性和安全性是两个是两个既有联系又不尽相同的概念。数据的完整性是为了防止数据库中存在不符合语义的数据，也就是为了防止被数据库中存在不正确的数据。数据的安全性是保护数据库防止恶意破坏和非法存取。

为维护数据得完整性，数据库管理系统必须能够实现如下功能。

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

2、提供完整性检查的方法

3、进行违约处理

关系数据库管理系统使得完整性约束控制成为其核心支持的功能，从而能够为所有用户和应用 提供一致的数据库完整性。

实体完整性

定义实体完整性--定义主码

关系模型的实体完整性在 create table 中用primary key定义。对单属性构成的码有两种说明方法，一种是定义列级约束条件，另一种是定义为表级约束条件。对多个属性列构成的码只有一种说明方法，即定义为表级约束条件。

【例】将Student表中的Sno属性定义为码。

create table Student
(
    Sno char(9) primary key,     /*在列级定义主码*/
    Sname char(20)  not null,
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
);

create table Student
(
    Sno char(9),     
    Sname char(20)  not null,
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
    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) /*只能在表级定义主码*/
);

实体完整性检查和违约处理

用primary key短语定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，关系数据库系统会根据实体的完整性规则自动进行检查。包括：

（1）检查主码值是否唯一，如果不唯一则拒绝插入或修改。

（2）检查主码的各个属性是否为空，只要有一个为空则拒绝插入或修改。

检查记录中主码值是否唯一的一种方法是进行全表扫描，依次判断表中每一条记录的主码值与将插入的记录的主码值是否相同。

全表扫描是十分耗时的，为了避免对基本表进行全表扫描，关系数据库管理系统一般都在主码上自动建立一个索引。

定义参照的完整性--定义外码

关系模型的参照完整性在create table 中用foreign key短语定义哪些列为外码，用references短语指明了外码参照哪些表的主码。

【例】关系SC中的一个元组表示一个学生选修了某门课程的成绩，（Sno，Cno）是主码，Sno、Cno分别参照引用Student表的主码和Course表的主码。

定义SC表中的参照完整性。

create table SC
(
    Sno char(9) not null,
    Cno char(4) not null,
    grade samllint,
    primary key(Sno,Cno),  /*在表级定义实体完整性*/
    foreign key (Sno) references Student(Sno),/*在表级定义参照完整性*/
    foreign key (Cno) references Course(Cno), /*在表级定义参照完整性*/
);

参照完整性检查和违约处理

参照 完整性将两个表中的相应元组联系起来了。因此，对被参照表和参照表进行增、删、改操作时有可能破坏参照完整性，必须进行检查以保证这两个表的相容性。

可能破坏参照完整性的情况及违约处理
被参照表参照表违约处理
可能破坏参照完整性插入元组拒绝
可能破坏参照完整性修改外码值拒绝
删除元组可能破坏参照完整性拒绝/级联删除/设置为空值
修改主码值可能破坏参照完整性拒绝/级联删除/设置为空值

当上述的不一致发生时，系统可以采用以下策略加以处理：

（1）拒绝执行

（2）级联操作

（3）设置为空值

用户定义的完整性

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。目前的关系数据库管理系统都提供了定义和检验这类完整性的机制，使用了和实体完整性、参照完整性相同的技术和方法来处理它们，而不必应用程序承担这一功能。

属性上的约束条件

1、属性上约束条件的定义

在create table 中定义属性的同时，可以根据要求定义属性上的约束条件，即属性值的限制，包括：

（1）列值非空 not null

（2）列值唯一 unique

（3）检查列值是否满足一个条件表达式 check语句

【例】不允许取空值

在定义SC表时，说明Sno，Cno，Grade属性上不允许取空值

create table SC
(
    Sno char(9) not null,     /*Sno属性不允许取空值*/
    Cno char(4) not null,     /*Cno属性不允许取空值*/
    grade smallint not null,  /*grade属性不允许取空值*/
    primary (Sno,Cno)         /*在表级定义实体完整性，隐含了Sno、Cno不允许取空值，在列值不允许取空值可以不写*/
) ;

【列】列值唯一

建立了部门表dept，要求部门名称Dname列值取唯一，部门编号Deptno列为主码。

create table DEPT
(
    Deptno numeric(2),
    Dname char(9) unique not null, /*要求列值唯一，且不能取空值*/
    Location char(10),
    primary key (Deptno)
);

【例】用check短语制定了列值应满足的条件

Student表Ssex只允许取“男”、“女”。

create table Student
(
    Sno char(9) primary key,  /*在列级定义主码*/
    Sname char(8) not null,   /*Sname属性不允许取空值*/
    Ssex char(2) check (Ssex in ('男','女')), /*性别属性Ssex只允许取‘男’或者‘女’*/
);

2、属性上约束条件的检查和违约处理

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。

元组上的约束条件

1、元组上约束条件的定义

与属性上的约束条件的定义类似，在create table 语句中可以用check短语定义元组上的约束条件，即元组级的限制。同属性限制相比，元组级的限制可以设置不同属性之间取值的相互约束条件。

【例】当学生的性别为男时，其名字不能Ms.打头

create table Student
(
    Sno char(9),
    Sname char(8) not null,
    Ssex char(3),
    Sage samllint,
    Sdept char(20),
    primary key (Sno),
    check (Ssex='女' or Sname not like 'Ms.%')
);

2、元组上约束条件的检查和违约处理

当往表中插入元组或修改属性值时，关系数据库管理系统将检查元组上的约束条件是否被满足，如果不满足则操作拒绝执行。

完整性约束命名子句

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中的约束条件，要求学号改在900000~999999之间，年龄由小于30改为小于40。

可以先删除原来的约束条件，在增加新的约束条件。

/*删除原约束条件C1*/
alter table Student 
    drop contraint C1;

/*新增约束条件*/
alter table Student 
    add constraint C1 check (Sno between 900000 and 999999);

/*删除原约束条件C3*/
alter table Student 
    drop contraint C3;

/*新增约束条件*/
alter table Student 
    add constraint C3 check (Sage<40);

 断言

在SQL中可以使用数据定义语言中的create assertion 语句，通过声明性断言来指定更具有一般性的约束。刻印定义涉及多个表或聚集操作的比较复杂的完整性约束。

1、创建断言的语句格式

create assertion <断言名> <check>子句

每个断言都被赋予一个名字，<check 子句>中的约束条件与where子句的条件表达式类似。

【例】限制数据库课程最多60名学生选修

create assertion ASSE_SC_DB_NUM
    check (60>= (select count(*)
                 from Course,SC
                 where SC.Cno=Course.Cno and Course.Cname='数据库')
          );

触发器

触发器是用户定义在关系表上的一类由事物驱动的特殊过程。一旦定义，触发器将被保存在数据库服务器中。任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。

定义触发器

触发器又叫做事件-条件-动作 规则。当特殊系统发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。

SQL使用create trigger 命令建立触发器，其一般格式为：

create trigger <触发器名>   

{before| after} <触发事件> on <表名>   

referencing new | old row as <变量>   

for each {row | statement}   

[ with <触发条件> ] <触发动作体> 

 下面对定义触发器的各部分语法进行详细说明。

（1）只有表的拥有者，即创建表的用户才能在表上创建触发器，并且一个表上只能创建一定数量的触发器。触发器的具体数量由具体的关系数据库管理系统在设计时确定。

（2）触发器名

        触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的，并且触发器名和表名必须在同一模式下。

（3）表名

触发器只能定义在基本表上，不能定义在视图上。

（4）触发事件

        触发事件可以是insert、delete或update。也可以是包含这几个事件的组合。after/before是触发时机。after表示在触发时间的操作执行之后激活触发器；before表示在触发事件的操作执行之前激活触发器。

（5）触发器类型

        触发器按照所触发动作的间隔尺寸可以分为行级触发器和语句级触发器。

【例】定义一个触发器，当在SC表中作更新和插入操作时，如果学生成绩小于60，自动改为60.

create trigger insert_or_update_grade
on SC
for insert uodate
as
    update SC set grade =60
    from inserted i
    where SC.Sno=i.Sno and SC.Cno=i.Cno and i.grade<60;

删除触发器

删除触发器的SQL语法如下：

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

 触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。