数据库关系模型（约束和触发器）

Myth丶恋晨 2021-09-21 06:42 572阅读 0赞

## 一、基本概念 ##

>   **1.基本术语（重要，记住！！！）**

• **关系** ：是用于描述数据的一张二维表，组成表 的行称为元组，组成表的列称为属性。

• **域** （ Domain ）：指列（或属性）的取值范围。

• **候选键** （ Candidate  Key ）：也称为候选码。能唯一的标识关系中每一个元组的最小属性集。一个关系可能有多个候选键。

• **主键** （ Priamary  Key ， PK ）：也称为主码。一个唯一识别关系中元组的最小属性集合。可以从关系的候选键中，指定其中一个作为关系的主键。一个关系最多只能指定一个主键。 要求作为主键的列不允许取 NULL 值 。

• **主属性** ：候选键中所有的属性均称为主属性。

• **非主属性** ：不包含在任何候选键中的属性称为非主属性。

• **全码** ：关系中所有属性的组合是该关系的一个候选码，则该候选码称为全码。

• **外键** （ Foreign  Key ， FK ）：关系 R 中的某个属性 K 是另一个关系 S 中的主键，则称该属性 K 是关系 R 的外键。通过外键可以建立两表间的联系。

## 二、数据库完整性 ##

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

为了维护数据库的完整性，DBMS必须能够：

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

（2）提供完整性检查的方法

（3）违约处理

### 1.三类完整性规则 ###

> **1.实体完整性规则**

**          实体完整性给出了主键的取值的最低约束条件。**

**          规则****4.1 ** **主键的各个属性都****不能为空值****。**

> **2.参照完整性**

参照完整性给出了在关系之间建立正确的联系的约束条件。

**         定义****4.1**  设F是关系R的一个或一组属性（但F不是R的主键），K是关系S的主键。如果F与K相对应，则称F是关系R的外               键，并称关系R为参照关系，关系S为被参照关系。而关系R和关系S可以是同一个关系。

**        规则****4.2**  外键或者取空值（要求外键的每个属性均为空值），或者等于被参照关系中的主键的某个值。

> **3.用户定义的完整性**

**        规则****4.3**  属性的取值应当满足用户定义的约束条件。

DBMS应该提供定义和检验这类完整性的机制（如约束Check、触发器Trigger等），以便用统一的方法来处理它们，而不应          该由应用程序来承担这个功能。

### 2.MySQL提供的约束 ###

> **1.主键（PRIMARY KEY）约束**

**        主键约束主要是针对主键，以保证主键值的完整性。主键约束要求主键值必须满足两个条件：**

**      （****1****）值唯一；**

**      （****2****）不能为空值。**

**        主键约束分列级和表级两种定义方式。列级针对表中一列，而表级则针对同一表中个一列或多列。**

#创建表的同时，指定主键约束
    DROP TABLE IF EXISTS employee;
    CREATE TABLE employee
    ( empno DECIMAL(2),
      ename VARCHAR(8),
    	age DECIMAL(3),
    	deptno DECIMAL(2),
    	CONSTRAINT pk_no PRIMARY KEY(empno,deptno)
    );
    DESC employee;
    #删除主键约束
    ALTER TABLE employee
    DROP PRIMARY KEY;
    DESC employee;
    #添加主键约束
    ALTER TABLE employee
    ADD CONSTRAINT pk_no PRIMARY KEY(empno);
    DESC employee;

> **2.唯一约束**

唯一约束主要是针对于候选键，以保证候选键值的完整性。唯一约束要求候选键满足2个条件：

（1）值唯一；

（2）可有一个且仅有一个空值。

（1）建立employee表，在employee表中定义一个phone字段，并为phone字段定义指定名称的唯一约束。

CREATE  TABLE  employee
    ( empno   DECIMAL(2)  PRIMARY  KEY,
      name    VARCHAR(8),
      age     DECIMAL(3),
      phone   VARCHAR(12),
      deptno  DECIMAL(2),
      CONSTRAINT  emp_phone  UNIQUE(phone)
    );

（2）删除唯一约束emp\_phone。

ALTER TABLE employee
    
     DROP  INDEX  emp_phone;

（3）为已有表employee根据phone字段创建唯一约束，约束名为emp\_phone。

ALTER  TABLE  employee
    
     ADD  CONSTRAINT  emp_phone   UNIQUE(phone);

> **3．检查约束**

MySQL数据库中可以设置检查约束，但是检查约束在表中是不生效的，也就是说仍然可以插入不符合条件的数据。

DROP TABLE IF EXISTS employee;
    CREATE TABLE  employee
    ( empno  DECIMAL(2) PRIMARY KEY,
     ename   VARCHAR(8),
     age    DECIMAL(3),
     phone  VARCHAR(12)  UNIQUE,
     deptno  DECIMAL(2),
     CONSTRAINT age_CK  CHECK (age>20 AND age<60)
    );
    DESC employee;
    INSERT INTO employee(empno,ename,age)VALUES(10,'Jack',70);
     
    SELECT * FROM employee;

结果：

![20200422164117247.png][]

> **4.外键约束**

外键约束涉及到两个表，即主表和从表，从表是指外键所在的表，主表是指外键在另一张表中作为主键的表。

外键约束要求：被定义为外键的字段，其取值只能为主表中引用字段的值或NULL值。

**          Q:对主表主键进行** **INSERT** **、** **DELETE** **、** **UPDATE** **操作，会对从表有什么影响呢？**

（1）插入（INSERT）

主表中主键值的插入，不会影响从表中的外键值。

（2）修改（UPDATE）

如果从表中的外键值与主表中的主键值一样的话，主表中主键值的修改要影响从表中的外键值。

（3）删除（DELETE）

主表中主键值的删除，可能会对从表中的外键值产生影响，除非主表中的主键值没有在从表中的外键值中出现。

**Q:****对从表外键的操作对完整性有什么影响呢？**

（1）插入（INSERT）

插入从表的外键值时，要求插入的外键值应“参照”（REFERENCE）主表中的主键值。

（2）修改（UPDATE）

修改从表的外键值时，要求修改的外键值就“参照”主表中的主键值。

（3）删除（DELETE）

从表中元组的删除不需要参照主表中的主键值。

>  实现表间数据完整性的维护，可有以下两种方式。

（1）利用外键约束定义，即在表上定义外键约束，来完成主表和从表间两个方向的数据完整性。

（ 2 ）利用触发器完成两表间数据完整性的维护，即主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从                       表到主表方向的参照完整性。

![20200423143126414.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjc5ODE4_size_16_color_FFFFFF_t_70][]

(1）建立employee和department表，实现两表间的外键约束。

**       **

DROP TABLE IF EXISTS employee;
    DROP TABLE IF EXISTS department;
    CREATE  TABLE  department
    ( deptno     DECIMAL(5)  PRIMARY  KEY,
      dept_name  VARCHAR(16)
    );
    CREATE  TABLE  employee
    ( empno   DECIMAL(5)  PRIMARY  KEY,
      name    VARCHAR(10)  NOT  NULL,
      age     DECIMAL(3),
      deptno  DECIMAL(5),
    CONSTRAINT  FK_ID  
    FOREIGN  KEY(deptno)  REFERENCES  department(deptno) 
    ON  UPDATE  CASCADE
    );
    DESC department;
    DESC employee;

（2）删除employee表上的FK\_ID约束。

ALTER  TABLE  employee
      DROP  FOREIGN  KEY  FK_ID;

### **     ** **级联删除** ###

练习：创建一张表 render，render数据复制题目中借阅表数据，并添加外键（借阅证号）参考读者表 （借阅证号），当读者表删除一条数据时，render 表对应的外键所在行信息也会删除。

CREATE TABLE render SELECT * FROM borrow;
    ALTER TABLE render 
      ADD CONSTRAINT FK_ID FOREIGN KEY(borrnum)
    	REFERENCES reader(borrnum)
    	ON DELETE CASCADE;
    DESC render;

**ON UPDATE CASCADE 级联更新**

**ON DELETE CASCADE 级联删除**

### **3.触发器** ###

利用AFTER触发器维护表间的数据一致性。

具体做法是：主表和从表应分别建立各自的触发器，主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从表         到主表方向的参照完整性。

这里的主表和从表不需要定义外键，只需要有共同的列即可。

**触发器创建的语法如下：**

**CREATE  TRIGGER  触发器名**

**  BEFORE | AFTER **

**  INSERT | DELETE | UPDATE**

**  ON   表名**

**  FOR  EACH  ROW**

**        < 触发体 >**

【例4-7】创建触发器delete\_trigger触发器，触发器将记录哪些用户删除了department表中的数据，以及删除的时间。

CREATE TABLE merch_log
    (who VARCHAR(30),
    oper_date  DATE
    );
    CREATE   TRIGGER  delete_trigger
      AFTER  DELETE
      ON  department
      FOR  EACH ROW
      INSERT  INTO  merch_log(who,oper_date) 
                   VALUES(USER(),SYSDATE());
    
    SELECT * FROM merch_log;

为了测试该触发器是否正常运行，在department表中删除10号部门的记录；并查询日志信息表merch\_log。

DELETE  FROM  department  WHERE  deptno=11;
    
    SELECT  *  FROM  merch_log;

练习：创建触发器tigger\_db，该触发器实现当删除读者表中记录时，一并删除借阅表中关于该读者的数据。

DROP TRIGGER IF EXISTS trigger_db;
    CREATE TRIGGER trigger_db
    AFTER DELETE ON reader FOR EACH ROW 
    DELETE FROM borrow WHERE borrnum=old.borrnum;
    DELETE FROM reader WHERE borrnum=125;

[20200422164117247.png]: /images/20210920/1f6d8c99f2924a53970cae52f76c7e25.png
[20200423143126414.png]: /images/20210920/c0fc8e19825046ccab4b28cd0388860f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjc5ODE4_size_16_color_FFFFFF_t_70]: /images/20210920/7ac337599b1540a3a66e2f7f7c048ae9.png