数据库完整性

最新推荐文章于 2022-04-27 08:30:18 发布

五山小新新

最新推荐文章于 2022-04-27 08:30:18 发布

阅读量822

点赞数

分类专栏：数据库系统概论

本文链接：https://blog.csdn.net/Al_xin/article/details/38732645

版权

数据库系统概论专栏收录该内容

9 篇文章 0 订阅

订阅专栏

数据库完整性

数据库的完整性
- 数据的正确性和相容性
数据的完整性和安全性是两个不同概念
- 数据的完整性
  - 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
  - 防范对象：不合语义的、不正确的数据
- 数据的安全性
  - 保护数据库防止恶意的破坏和非法的存取
  - 防范对象：非法用户和非法操作
实体完整性
- 关系模型的实体完整性
  - CREATE TABLE中用PRIMARY KEY定义
- 单属性构成的码有两种说明方法
  - 定义为列级约束条件
  - 定义为表级约束条件
- 对多个属性构成的码只有一种说明方法
  - 定义为表级约束条件
- 插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：

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

检查记录中主码值是否唯一的一种方法是进行全表扫描

参照完整性

关系模型的参照完整性定义

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码

用REFERENCES短语指明这些外码参照哪些表的主码

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

［例3］定义SC中的参照完整性

          SQL Code  
       

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
27
28
29

CREATE TABLE SC
  (
    Sno CHAR
    (
    )
     NOT NULL， Cno CHAR
    (
    )
     NOT NULL， Grade SMALLINT， PRIMARY KEY
    (
      Sno， Cno
    )
    ， FOREIGN KEY
    (
      Sno
    )
     REFERENCES Student
    (
      Sno
    )
    ， FOREIGN KEY
    (
      Cno
    )
     REFERENCES Course
    (
      Cno
    )
  )

用户定义的完整性
- 属性上的约束条件的定义
  - CREATE TABLE时定义
  - 列值非空（NOT NULL）
  - 列值唯一（UNIQUE）
  - 检查列值是否满足一个布尔表达式（CHECK）
- 属性上的约束条件检查和违约处理
  - 插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足
  - 如果不满足则操作被拒绝执行
- 元组上的约束条件的定义
  - 在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
  - 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件
- 元组上的约束条件检查和违约处理
  - 插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足
  - 如果不满足则操作被拒绝执行

例7 用check段语句指定列值应该满足条件。Student表的Ssex中只允许“男”或者“女”。

     SQL Code  
  

1
2
3
4
5
6
7

create table Student
(Sno char( 9) primary key,
Sname char( 8) not null,
Ssex char( 2) check(Ssex in ( '男', '女')),
Sage smallint,
Sdept char( 20)
);

例9：当学生的性别是男时，其名字不能以Ms.打头。

     SQL Code  
  

1
2
3
4
5
6
7
8
9

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.%');
);

完整性约束命名子句
- CONSTRAINT 约束
  
  CONSTRAINT <完整性约束条件名>
  
  ［PRIMARY KEY短语
  
  |FOREIGN KEY短语
  
  |CHECK短语］

例11 ：建立教师表teacher，要求每个较适合的应发工资不低于3000元。

应发工资实际上就是实发工资列Sal与扣除项Deduct 之和。

     SQL Code  
  

1
2
3
4
5
6
7
8
9
10

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 EMPKey foreign key(Depto) references dept(Deptno),
constraint C1 check(Sal+Deduct>= 30)
);

- 使用ALTER TABLE语句修改表中的完整性限制

例13 修改表Student中的约束条件，要求学号改为在900000-999999之间，年龄有小于30改成小于40.

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

     SQL Code  
  

1
2
3
4

alter table Student drop constraint C1;
alter table Student add constraint C1 check(Sno between 90000 and 99999);
alter table Student drop constraint C3;
alter table Student add constraint C3 check(Sage< 40);

- SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该域应该满足的完整性约束条件。
触发器
- 定义触发器
  - CREATE TRIGGER语法格式
    
    CREATE TRIGGER <触发器名>
    
    {BEFORE | AFTER} <触发事件> ON <表名>
    
    FOR EACH {ROW | STATEMENT}
    
    ［WHEN <触发条件>］
    
    <触发动作体>

触发器：是用户定义在关系表上的一类由时间驱动的特殊过程。一旦定义，任何用户对表的增，删，查，改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施比foreign key约束、check约束更加复杂的检查和操作，具有更精细和更强大的数据控制能力。

- 定义触发器的语法说明:
  - 1. 创建者：表的拥有者
  - 2. 触发器名
  - 3. 表名：触发器的目标表
  - 4. 触发事件：INSERT、DELETE、UPDATE
  - 5. 触发器类型
    - 行级触发器（FOR EACH ROW）
    - 语句级触发器（FOR EACH STATEMENT）
  - 6. 触发条件
    - 触发条件为真
    - 省略WHEN触发条件
  - 7. 触发动作体
    - 触发动作体可以是一个匿名PL/SQL过程块
    - 也可以是对已创建存储过程的调用
- 激活触发器
  - 触发器的执行，是由触发事件激活的，并由数据库服务器自动执行
  - 一个数据表上可能定义了多个触发器
    - 同一个表上的多个触发器激活时遵循如下的执行顺序：
    - （1）执行该表上的BEFORE触发器；
    - （2）激活触发器的SQL语句；
    - （3）执行该表上的AFTER触发器。
- 删除触发器
  - 删除触发器的SQL语法：
    
    DROP TRIGGER <触发器名> ON <表名>;
  - 触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。

例18：定义一个before行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改成4000元”。

     SQL Code  
  

1
2
3
4
5
6
7
8

create trigger Inset_Or_Update_Sal
before insert or update on Teacher
for each row
as begin
   if( new.Job= '教授') and ( new.Sal< 4000) then
      new.Sal:= 4000;
   end if;
end;

例19：定义after行级触发器，当教师表Teacher的工资发生变化后就自动在工资变化表Sal_log中增加一条相应的记录。

1、建立表。

     SQL Code  
  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

create table Sal_log
(Eno numeric( 4) references Teacher(Eno),
Sal numeric( 7, 2),
Username char( 10),
Date timestamp
);
create trigger Insert_Sal
after insert on Teacher
for each row
as begin
   insert into Sal_log value
  ( new.Eno, new.Sal, current_user, current_timestamp);
   end;
create trigger Update_Sal
   after update on Teacher
   for each row
   as begin
   if( new.Sal<> old.Sal) then insert into Sal_log value
  ( new.Eno, new.Sal, current_user, current_timestamp);
   end if;
end;

例21 删除教师表Teacher上的触发器Insert_Sal。