数据库完整性-5

五. 数据库完整性

• 数据完整性

  • 数据的正确性
    • 是指数据是否符和现实世界语义，反映了当前实际状况
  • 数据的相容性
    • 是指数据库同一对象在不同关系表中的数据是否是符合逻辑的

• 数据的完整性和安全性是不同的概念

  • 数据库的完整性
    • 防止数据库中存在不符合语义的数据，也就防止数据库中存在不正确的数据
    • 防范对象：不合语义、不正确的数据
  • 数据的安全性
    • 保护数据库防止恶意的破坏和非法的存取
    • 防范对象：非法用户和非法操作

  完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据

  安全性防范的是非法用户和非法操作存取数据库中的正确数据

• 为维护数据库的完整性，数据库管理系统必须

  1. 提供定义完整性约束条件的机制
  2. 提供完整性检查机制
  3. 违约处理
     • 拒绝(NO ACTION)执行该操作
     • 级联（CASCADE）执行该操作

  ---

5.1 实体完整性

5.1.1 实体完整性定义

关系模型的实体完整性

CREATE TABLE 中用 PRIMARY KEY定义

• 单属性构成码有两种说明方法
  • 定义为列级约束条件
  • 定义为表级约束条件
• 对多个属性构成的码只有一种说明方法
  • 定义为表级约束条件

【eg.5.1】将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)
);

【eg.5.2】将SC表中的Sno，Cno属性定义为码

CREATE TABLE SC
( Sno CHAR(9) NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT, 
  PRIMARY KEY(Sno,Cno) /* 只能在表级定义主码*/
 );

---

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

• 插入或者对主码列进行更新操作时检查
  • 检查主码值是否唯一，如果不唯一则拒绝插入或修改（进行全表扫描）
  • 检查主码的各个属性是否为空，只要有 一个为空就拒绝插入或修改

RDBMS完整性控制机制的三个组成部分

• 定义机制
• 检查机制
• 违约处理

5.2 参照完整性

5.2.1 参照完整性定义

• 关系模型的参照完整性定义
  • 在CREATE TABLE 中用FOREIGN KEY短语定义那些列为外码
  • 用REFERENCES短语指明这些外码参照那些表的主码

【eg.5.3】关系SC中(Sno,Cno)时主码，Sno，Cno分别参照Student表中的主码和Course表中的主码

CREATE TABLE SC
(  Sno CHAR(9) NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT, 
  PRIMARY KEY(Sno,Cno), /* 只能在表级定义主码*/
  FOREIGN KEY (Sno) REFERENCES Student(Sno), /*在表级定义参照完整性*/
  FROEIGN KEY (Cno) REFERENCES Course(Cno)  /*在表级定义参照完整性*/
 );

---

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

• 在对被参照表各参照表进行增删改操作时可能破坏参照完整性，必须进行检查
• 插入元组或修改外码只：拒绝
• 删除元组或修改主码值：拒绝/级联删除/设置为空值

---

5.3 用户定义的完整性

5.3.1 属性上的约束条件

• 用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求

• 关系数据库管理系统提供了定义和检验用户定义完整性的机制

• CREATE TABLE 时定义属性上的约束条件

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

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

  • 插入元组或修改属性时，RDBMS检查到属性上的约束条件是否满足

  • 如果不满足则该操作被拒绝执行

---

5.3.2 元组上的约束条件

• 属性上的约束条件：只涉及单个属性

• 元组级的限制：可以设置不同属性之间的取自的相互约束条件

• 在CREATE TABLE 时可以用CHECK子句来定义元组上的约束条件

• 插入元组或者修改属性的值时，RABMS检查到元组上的约束条件是否被满足

• 如果不满足则拒绝执行

5.4 完整性约束命名字句

1. 完整性约束命名子句

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

   • 完整性约束条件包括

     NOT NULL ,UNIQUE.PRIMARY KEY短语,FOREIGN KEY短语,CHECK短语
     

【eg. 5.10】建立学生登记表Student，要求学号在90000_99999之间

姓名不能取空值，年龄小于30，性别只能取男女

CREAATE TABLE
( Sno NUMERIC(6)
     CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
  Sname CHAR(10)    CONSTRAINT C2 NOT NULL,
  Sage NUMERIC(3) c3 CHECK (Sage<30),
  Ssex CHAR(2) CONSTRAINT C4 CHECK(Ssex IN ('男','女'))，
  CONSTRAINT StudentKey PRIMARY KEY(Sno)    
);

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

【eg. 5.12】去掉Student中对性别的限制

ALTER TABLE Student
DROP CONSTRAINTC4;

【eg. 5.13】修改Student中的约束条件，要求学生改为在90000——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);

---

5.5 域中的完整性限制

5.5 断言

语法格式

CREATE ASSERTION <断言名><CHECK子句>

• 每个断言都被赋予一个名字
• CHECK子句 中的约束条件与WHERE子句的条件表达式类似

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

CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60>=(select count(*)
            FROM Cuorse,SC
            WHERE SC.Cno=Course.Cno AND
                  Course.name='数据库')
      )；

触发时机：向SC表中插入元组

违约反应：拒绝执行

【eg. 5.19】限制每一门课程最多60名学生选修

CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60>=(SELECT count(*)
           FROM SC
           GRUOP BY Cno)
     );

【eg. 5.20】限制每个学期每一门课最多60名学生选修

/*首先需要修改SC表的模式，增加一个‘学期’(TERM）属性*/
ALTER TABLE SC ADD TERM DATE;
/*然后定义断言*/
CREATE ASSERTION ASSE_SC_CNUM2
CHECK(60>=(SELECT count(*)
           FROM SC
           GROUP BY Cno,TERM)
     );

2. 删除断言的语句格式

   DROP ASSERTION <断言名>；
   

5.7 触发器

5.7.1 定义触发器

• 触发器

  • 用户定义在关系表上的一类由时间驱动的特殊过程
  • 任何用户对表的增删改操作均有服务器自动激活相应的触发器
  • 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

• CREATE TRIGGER语法格式

  CREATE TRIGGER<触发器名>
  {BEFORE|AFTER}<触发事件>ON<表名>
  REFERENCING NEW|OLD ROWS AS<变量>
  FOR EACH {ROW|STATEMENT}
  [WHERE <触发条件>]<触发动作体>
  

  三要素

  触发事件

  触发条件

  触发动作

  ---

5.7.2 激活触发器

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

1. 执行该表上的BEFORE触发器
2. 激活触发器的SQL语句
3. 执行该表上的AFTER触发器

5.7.3 删除触发器