数据库11-断言与参照完整性

Posted on

完整性约束

控制机制

完整性约束是加在数据库模式上的一个具体条件，规定什么样的数据能够存储到数据库中；
定义机制：

  • 约束条件：数据模型的组成部分，约束数据库的寓意
  • DBMS提供定义数据库完整性约束条件，并作为模式的一部分存入数据库

检查机制：

  • 检查用户发出的操作是否违背了完整性约束条件

违约反应：

  • 发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作保证数据完整性；

分类

  • 主键约束PRIMARY KEY ：一个关系仅一个，属性非空
  • 唯一约束UNIQUE：一个关系可声明多个，可为空，允许多个空值，不能定义在被主键约束的属性上
  • 非空约束NOT NULL
  • 自定义约束CHECK
  • 参照完整性约束FOREIGN KEY
  • 断言ASSERTION

还可以根据位置分类为列级，行级和表级约束；以下是一些表建立的约束的例子

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 Diagnosis{
DGno VARCHAR(10) PRIMARY KEY,
Pno VARCHAR(10) NOT NULL,
Dno VARCHAR(10) NOT NULL,
Symptom VARCHAR(100),
Diagnosis VARCHAR(100),
DGtime DATETIME,
Rfee DECIMAL(18,2) NOT NULL
};
CREATE TABLE Doctor{
Dno VARCHAR(10),
Dname VARCHAR(50) NOT NULL,
Dsex VARCHAR(2) CHECK( Dsex IN (‘男’, ‘女’)),
Dage INT CHECK( Dage > 0 AND Dage <60),
Ddeptno VARCHAR(10),
Dlevel VARCHAR(50),
Dsalary DECIMAL(18,2),
PRIMARY KEY(Dno),
};
CREATE TABLE RecipeDetail{
Rno VARCHAR(10),
Mno VARCHAR(10) NOT NULL,
Mamount DECIMAL(18,0),
PRIMARY KEY(Rno,Mno),
CHECK (Mno IN (SELECT Mno FROM Medicine))//这是参照完整性约束，允许出现其他关系的子查询
};

以下是断言的格式和一个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE ASSERTION fk_Diagnosis
CHECK 
( NOT EXISTS
( SELECT * 
FROM Diagnosis 
WHERE Dno NOT IN
(SELECT Dno
FROM Doctor))); 


CREATE ASSERTION <断言名> CHECK<谓词>
Create assertion salarycheck check(
Not exists(
Select * from Doctor x
Where Dsalary >= some ( select Dsalary from Doctor y 
Where x.Deptno=y.Deptno and y.Dno =(
Select Manager from Dept 
Where x.Deptno =Dept.Deptno)
)
);

参照完整性

概念

给定关系R，S，若存在R.A参照S.B

  • R是参照表，S为被参照表
  • A取值要么为空，要么来自于S.B
  • A为外码
  • B是S的主码
    若两张表的属性有参照完整性约束，在创建R表声明
1
2
REDERENCES S(B)
FOREIGN KEY(A) REFERENCES S(B)

可见对R和S的删除，更新，插入都有可能触发完整性违约；

  • DELETE FROM S
  • UPDETE S.B
  • UPDETE R.A
  • INSERT TO R

策略

  • 受限策略RESTRICTED:系统的默认方式，出现违约时，系统拒绝执行
  • 置空策略SET-NULL：根据环境的语义，外码可能为空
  • 级联策略CASCADE：不用拒绝用户操作请求的处理方式
  • 定义方式ON (DELETE|UPDATE) (SET NULL|CASCADE)