5.0 数据库完整性详解（PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER）

00：引用文章链接

==>>BitHachi

01：数据库完整性概述

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

（1）DBMS为维护完整性，必须实现如下功能：

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

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

• 一般在执行INSERT、UPDATE、DELETE时检查

3.违约处理

• 拒绝（no action）执行该操作
• 级连（cascade）执行其他操作

（2）完整性和安全性区别

1.数据的完整性

• 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
• 防范对象：不合语义的、不正确的数据

2.数据的安全性

• 保护数据库 防止恶意的破坏和非法的存取
• 防范对象：非法用户和非法操作

02：实体完整性—PRIMARY KEY

（1）实体完整性的定义

1.关系模型的实体完整性

• CREATE TABLE中用PRIMARY KEY定义

2.单属性构成的码

• 列级、表级

3.多属性构成的码

• 表级

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

插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：

• 主码值不唯一则拒绝插入或修改
• 主码的属性只要有一个为空就拒绝插入或修改

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

• 为避免耗时，在主码上自动建立一个索引

03：参照完整性—REFERENCES

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

（1）参照完整性定义

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

• FOREIGN KEY定义哪些列为外码
• REFERENCES指明这些外码参照哪些表的主码

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

参照完整性违约处理

• 拒绝(NO ACTION)执行
  一般设置为默认策略

• 级联(CASCADE)操作
  当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组

• 设置为空值（SET-NULL）
  当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值
  举例：

［例4］  显式说明参照完整性的违约处理示例
CREATE TABLE SC（
Sno   CHAR(9)  NOT NULL，
Cno   CHAR(4)  NOT NULL，
Grade  SMALLINT，
PRIMARY KEY（Sno，Cno）， /*在表级定义实体完整性，sno、Cno都不能取空值*/				
FOREIGN KEY (Sno) REFERENCES Student(Sno) 
ON DELETE CASCADE     /*级联删除SC表中相应的元组*/
ON UPDATE CASCADE， /*级联更新SC表中相应的元组*/

FOREIGN KEY (Cno) REFERENCES Course(Cno) 	                    
ON DELETE NO ACTION 	  /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE       /*当更新course表中的cno时，级联更新SC表中相应的元组*/
)；

• 注：以上对DELETE和UPDATE采用了不同的策略

• 经过测试，当UPDATE更新Student和Course表数据时，SC也自动更新

• 当删除Student的数据时，SC同步删除相应数据
  -当删除Course的数据时，拒绝删除，因为SC中有相应的外码数据，并设置了NO ACTION

• 当删除SC的数据时，对Student和Course无影响

  -对于参照完整性，除了应该定义外码外，还应定义外码列是否允许空值
  -当参照表和被参照表违反参照完整性时，系统选用默认策略，即拒绝执行。
  -如果想让系统采用其他策略时必须在创建参照表时显示地加以说明

04.用户定义的完整性—CHECK

• 用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求
• RDBMS提供，而不必由应用程序承担

（1）属性上的约束条件定义

• 在CREATE TABLE中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制

  • 列值非空（NOT NULL）
  • 列值唯一（UNIQUE）
  • 检查列值是否满足一个布尔表达式（CHECK（））

（2）属性上的约束条件检查和处理

• 插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足
• 如果不满足则操作被拒绝执行

（3）元组上的约束条件的定义

• 在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
• 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

［例9］  当学生的性别是男时，其名字不能以Ms.打头。
    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.%')
          /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
        )；
性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立；
当性别是男性时，要通过检查则名字一定不能以Ms.打头

（4）元组上的约束条件检查和违约处理

• 同属性

05：完整性约束命名子句—CONSTRAINT

（1）CONSTRAINT 约束语句格式：

constraint <完整性约束条件名><完整性约束条件>

（2）修改表中的完整性限制

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

［例13］  修改表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);

06：域中的完整性限制—DOMAIN

SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该域应该满足的完整性约束条件。

［例14］建立一个性别域，并声明性别域的取值范围
           CREATE DOMAIN GenderDomain CHAR(2)
           CHECK (VALUE IN ('男'，'女') );
这样［例10］中对Ssex的说明可以改写为Ssex GenderDomain

［例15］建立一个性别域GenderDomain，并对其中的限制命名
           CREATE DOMAIN GenderDomain CHAR(2)
           CONSTRAINT GD CHECK ( VALUE IN ('男'，'女') );
           
［例16］删除域GenderDomain的限制条件GD。
           ALTER  DOMAIN  GenderDomain  
           DROP CONSTRAINT GD;
           
［例17］在域GenderDomain上增加限制条件GDD。
          ALTER  DOMAIN  GenderDomain  
         ADD CONSTRAINT GDD CHECK (VALUE IN ( '1'，'0') ); 
         
通过［例16］和［例17］，就把性别的取值范围由('男'，'女')改为 ( '1'，'0') 

07：断言—ASSERTION

08：触发器—TRIGGER