【云驻共创】华为云数据库之数据库完整性

一、完整性约束

① “完整性约束”的概念

• 数据完整性约束是一组完整性规则的集合，为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
• 关系型数据库完整性约束包含：实体完整性、参照完整性和用户自定义完整性。
• GaussDB(forMVSQL)数据库：
• • 提供定义完整性约束条件的机制；
• • 提供完整性检查的方法；
• • 违约处理。

② 实体完整性约束

• 实体完整性(EntityIntegrity)：若属性（一个或一组属性）K是基本关系R的主码，则属性K的值在记录间不能重复，也不能取空值（主键取值非空且唯一）。
• 实体完整性可在CREATETABLE时用PRIMARYKEY定义，单属性构成的码即定义为列级和表级约束条件，多个属性构成的码即通过定义为表级约束条件进行说明。定义如下所示：
• • 创建表(school），并定义表级实体完整性：

CREATE TABLE `school`(
	`School_id`CHAR(1) NOT NULL COMMENT `学院编号`，
	`School_name`CHAR(4) NULL COMMENT `学院名称`,
	`School_dean`CHAR(6) NULL COMMENT `院长姓名`,
	`School_tel`CHAR(13) NULL COMMENT `EI`,
	`School_addr`CHAR(5) NULL COMMENT `地址`,
	PRIMARY KEY (`School id`))
	ENGINE = InnoDB
	DEFAULT CHARACTER SET = utf8mb4
	COLLATE = utf8mb4_general_ci
	COMMENT=`学院表`;

• 执行SQL命令，运行结果如下：

• • 创建表(department），并定义列级实体完整性：

CREATE TABLE `department`(
	`Department_id`CHAR(4) NOT NULL PRIMARYKEY COMMENT `系编号`,
    `Department_name`CHAR(14) NULL COMMENT `系名称`,
    `Department_dean` CHAR(6) NULL COMMENT `系主任`,
    `Teacher_num` SMALLINT UNSIGNED NULL COMMENT `教师人数`,
    `Class_num` SMALLINT UNSIGNED NULL COMMENT `班级个数`,
    `School_id`CHAR(1) NULL COMMENT `学院编号`）
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_general_ci
    COMMENT=`系表`;
)

• 实体完整性检查和违约处理：用PRIMARYKEY短语定义了关系的主码后，每当用户程序对基本表插入一条记录或者更新。主码时，DBMS按照实体完整性规则白动进行检查：
• • 检查主码值是否唯一，如果不唯一则拒绝插入或更新；
• • 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或更新。

③ 参照完整性约束

• 参照完整性(ReferenceIntegrity)：若属性（或属性组）F是基本关系尺的外码，它与基本关系S的主码KS相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须或者取空值，或者等于S中的某个元组的主码值（外码可以是空值，或存在关系问引用的另一个关系的有效值）。
• 参照完整性在CREATETABLE或ALTERTABLE时用FOREIGNKEY短语定义其为外码，用REFERENCES短语指明这些外码参照哪个表的主码。
• InnoDB允许使用CREATETABLE在表级约束条件上定义外键，该语法可以在CREATETABLE时使用，如果不指定CONSTRAINTsymbol,MYSQL会自动生成一个名字：

	[CONSTRAINT symbol] FOREIGN KEY(index col name, ...)
	REFERENCES tbl name (index col name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

• InnoDB允许使用ALTERTABLE在一个已经存在的表上增加一个新的外键；

	ALTER TABLE tbl name
    ADD [CONSTRAINT [symbol]]
    FOREIGN KEY(index col name, ...)
    REFERENCES tbl name (index col name,...)
    [ON DELETE reference option]
    [ON UPDATE reference option]

• InnoDB也支持使用ALTERTABLE来删除外键：

	ALTER TABLE tbl name
    DROP FOREIGN KEY fk symbol;

• 修改表(department），并定义表级参照完整性：

	ALTER TABLE department
    ADD CONSTRAINT school_id
   	foreign key(school id)
    REFERENCES school(school_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

• 执行SQL命令，运行结果，如下：

• 创建一个表（book），结构如上表所示，定义实体完整性约束，创建一个表(lendinglnfo），结构如下表所示，判断并定义实体及参照完整性约束：

• 创建表(book），并定义实体完整性约束：

CREATE TABLE 'book'(
	'Book_id' CHAR(8) NOT NULL PRIMARYKEY COMMENT '图书编号'，
    'Title' CHAR(3O) NOT NULL COMMENT '书名',
    'Press' CHAR(5O) NULL COMMENT '出版社',
    'Author' CHAR(100) COMMENT '作者',
    'Price' FLOAT COMMENT '价格'）
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_general_ci
    COMMENT= '图书表';

• 删除表（lendinglnfo）的参照完整性：

ALTER TABLE lendingInfo
DROP FOREIGN KEY Book_id

• 参照完整性检查和违约处理，一个参照完整性将两个表中的相应元组联系起来了。因此，在对主表和相关表进行添加、修改或删除操作时，有可能破坏参照完整性，必须进行检查。当不一致发生时，系统可以采用如下的策略加以处理：
• • 拒绝(NOACTION）执行：不允许该操作执行，该策略一般设置为默认策略；
• • 级联(CASCADE）操作：当删除或修改主表的一个元组，造成与相关表的不一致，则删除或修改相关表中所有不一致的元组；
• • 设置为空值：当删除或修改主表的一个元组，造成与相关表的不一致，则将相关表中所有不一致的元组的值设定为空值。

④ 用户自定义完整性约束

• 用户自定义完整性(User-DefinedIntegrity）是用户自行定义的，不属于其它完整性的所有规则。用户定义的完整性就是针对某一具体应用的数据心须满足的语义要求。
• 在CREATETABLE语句中定义属性的同时，可以根据应用要求，定义属性上的约束条件，即属性值限定，包括：
• • 列值非空(NOTNULL)；
• • 值唯一(UNIQUE)；
• • 检查列值是否满足一个布尔表达式(CHECK)。
• 用户自定义完整性检查和违约处理：
• • 插入元组或修改属性的值时，DBMS检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行；
• • 在CREATETABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制，同属性值限制相比，元组级的限制可以设置不同属性之问的取值的相互约束条件。
• 例如，创建表(book），结构如下表所示，并且定义完整性规则，剩余册数不高于总册数：

• 创建示例如下：

CREATE TABLE 'book' (
	'Book_id' CHAR(8) NOT NULL PRIMARY KEY COMMENT '图书编号',
    'Title' CHAR(30) NOT NULL COMMENT '书名',
    'Press' CHAR(50) NULL COMMENT '出版社',
    'Author' CHAR(100) COMMENT '作者',
    ‘Price' FLOAT COMMENT '价格',
    'RemainingNum'SMALL INT COMMENT '剩余册数',
    'TotalNum' SMALL INT COMMENT '总册数',
    CHECK(RemainingNum<=TotalNum))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4 general ci
    COMMENT = '图书表';
)

• 执行SQL命令，运行结果，如下图所示:
  
• 用户自定义完整性检查和违约处理：
• • 插入元组或修改属性的值时，DBMS检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行；
• • 在CREATETABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之问的取值的相互约束条件。

二、触发器

① 概念

• 触发器（Trigger）是一种特殊类型的存储过程，触发器采用事件驱动机制，当某个触发事件发生时，定义在触发器中的功能将被DBMS自动执行。
• 触发器是一个功能强大的工具，它与表紧密相连，在表中数据发生变化时自动强制执行。
• 触发器可以用于完整性约束、默认值和和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。当一个触发器建立后，它作为一个数据库对象被存储。

② 常用触发器种类

• INSERT触发器：向表中插入某一行时被激活，可以通过INSERT、RESET、UPLOAD、REPLACE触发；
• UPDATE触发器：再更改表中的数据的时候被激活，可以通过UPDATE触发；
• DELETE触发器：在表中被删除某一行的时候被激活，可以通过DELETE、REPLACE触发。

③ 触发器功能

• 触发器不需要手动调用，当触发事件发生时会自动执行；
• 触发器代码可以引用事件中对于行修改前后的值；
• 对于UPDATE事件可以定义对哪个表、或表中的哪一列被修改时，触发器被触发；
• 可以用IF子句来指定执行条件，触发器功能代码只有在条件成立时才执行；
• GaussDB只支持行级触发器；
• 触发器可以完成一些复杂的数据检查，可以实现某些操作的前后处理等。

④ 触发器优点

• 触发器能够实施比“外键约束”、"检查约束“和“检查约束”和“规则”等对象更为复杂的数据完整性检验，从而保证数据增删改查的时候的数据完整性；
• 和约束相比，触发器提供了更多的灵活性。约束将系统错误信息返回给用户，但这些错误并不是总能有帮助，而触发器则可以打印错误信息，调用其他存储过程，或根据需要纠正错误；
• 无论对表中的数据进行何种修改，增加、删除或更新，触发器都能被激活，对数据实施完整性检查；
• 触发器可通过数据库中的相关表实现级联更改；
• 触发器可以强制用比CHECK定义的约束更为复杂的约束，与CHECK约束不同，触发器可以引用其它表中的列；
• 触发器可以评估数据修改前后的表状态，并根据其差异采取对策；
• 一个表中的多个同类触发器(INSERT、UPDATE或DELETE）允许采取多个不同的对策以响应同一个修改语句；

⑤ 触发器缺点

• 触发器只能提供扩展验证，并且无法替换所有验证，一些简单的验证必须在应用层完成；
• 从客户端应用程序调用和执行触发器不可见，因此很难弄清数据库层中发生的情况；
• 触发器可能会增加数据库服务器的开销。

三、触发器的使用

① 创建触发器的四个要素

• 监视地点(table)；
• 监视事件(insert/update/delete)；
• 触发时机(after/before）；
• 触发事件(trigger body)；

② 创建触发器的方式

• 通过GaussDB(forMySQL)管理控制平台新建触发器窗口创建；
• 通过CREATE TRIGGER语句创建；
• 通过GaussDB(forMySQL)管理控制平台新建触发器窗口创建触发器：

• 创建只有一条执行语句的触发器：CREATE TRIGGER trigger name trigger time trigger event ON tb_name FOR EACH ROW一条执行语句；
• 创建有多条执行语句的触发器：

CREATE TRIGGER trigger name trigger time trigger event ON tb_name FOR EACH ROW
BEGIN
	执行语句列表
END

③ 创建触发器种类

• 根据触发时机与触发事件的组合，GaussDB(forMysQL）可以创建以下6种触发器：

BEFORE INSERT, BEFORE DELETE, BEFORE UPDATE
AFTER INSERT, AFTER DELETE, AFTER UPDATE

• 基于教师表(teacher）创建一个触发器(tri_teacherlnsert）约束“职称〞列只允许取“教授”、“副教授”、“讲/师”和“助教”：

• 具体实现如下：

DELIMITER $
CREATE trigger tri_teacherlnsert
AFTER INSERT
ON teacher FOR EACH ROW
BEGIN
	IF NEW.title NOT IN('教授', '副教授', '讲师', '助教')
    THEN DELETE FROM teacher WHERE teacher id=new.teacher id;
    END IF:
END $
DELIMITER;

• 执行SQL命令，运行结果，如下：

④ 修改触发器方式

• GaussDB(forMySQL)可以通过管理控制平台修改触发器窗口对已经存在的触发器进行修改；
• GaussDB(forMySQL）没有提供修改触发器的语句，想要通过语句修改触发器只能通过删除原触发器，再以相同的名称创建新的触发器的方式进行。

⑤ 删除触发器的方式

• 通过GaussDB(forMySQL)管理控制平台触发器窗口删除；

• 通过DROP TRIGGER语句删除；其中，数据库名为可选项，指定触发器所在的数据库的名称。若没有指定，则为当前默认的数据库,IF EXISTS为可选项，避免在没有触发器的情况下删除触发器：

DROPTRIGGER[IFEXISTS][数据库名]＜触发器名＞

• 删除一个表的同时，也会自动删除该表上的触发器；
• 删除约束“职称〞列插入数据触发器(tri_teacherinsert)：

• DROP trigger tri_teacherlnsert，执行SQL命令，运行结果，如下：

• DROP trigger teacherlnsert，执行SQL命令，运行结果，如下：

• DROP trigger IF EXISTS teacherlnsert，执行SQL命令，运行结果，如下：

四、存储过程

① 存储过程的概念

• 存储过程(StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，通过调用存储过程的名称并给定参数来执行。
• 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行，一次编译，多次执行，具有很好的执行效率。

② 存储过程种类

• 不带参数存储过程；
• 带参数存储过程：
• • 带输入参数的存储过程；
• • 带输出参数的存储过程；
• • 带输入和输出参数的存储过程。

③ 存储过程功能

• 可以进行变量声明；
• 能够集成SQL命令；
• 使用流程控制命令；
• 使用内部函数；

④ 存储过程优点

• 灵活性强；
• 保证安全性和完整性；
• 执行效率高；
• 降低网络通信量；

⑤ 存储过程缺点

• 代码编辑环境差；
• 缺少兼容性；
• 重新编译问题；
• 维护麻烦。

⑥ 创建存储过程

• 使用CREATE PROCEDURE语句创建存储过程：

CREATEPROCEDURE[数据库名』过程名(UNIOUTINOUT]参数名数据类型
[<特征＞.]
BEGIN
	过程体
END

• 使用CALL语句调用存储过程：

CALL[数据库名.]过程名[参数，...]）

• 创建存储过程（优秀学生）挑选各科成绩在80分以上的学生信息（不带参数的存储过程）：

DELIMITER $
CREATE PROCEDURE '优秀学生'()
BEGIN
    SELECT * FROM student ST,score SC
    WHERE ST.student id=SC.student id AND SC.score> =80;
END$
DELIMITER;

• 执行SQL命令，运行结果，如下图：

⑦ 修改存储过程方式

• GaussDB(forMySQL)可以通过管理控制平台修改存储过程窗口对已经存在的存储过程进行修改；
• 通过ALTER PROCEDURE语句修改存储过程的某些特征；
• GaussDB(forMySQL)没有提供修改存储过程名称和内容的语句，想要通过语句修改存储过程名称和内容只能通过先删除再新建的方式进行。

ALTER PROCEDURE <过程名＞[<特征> ...]
SQL SECURITY: DEFINER; INVOKER
确定性：DETERMINISTIC;NOTDETERMINISTIC
数据访问：CONTAINSSQL;NOSQL;READSSQL;MODIFIESSQLDATA
添加注释：COMMENT'字符串'

• 通过GaussDB(forMySQL)管理控制平台修改存储过程窗口修改：

⑧ 删除存储过程的方式

• 通过GaussDB(forMySQL)管理控制平台存储过程窗口删除；
• 通过DROP PROCEDURE语句删除：

DROP PROCEDURE[IF EXISTS]<过程名＞

• 通过GaussDB(forMySQL)管理控制平台存储过程窗口删除：

本文整理自华为云社区【内容共创】活动第15期：https://bbs.huaweicloud.com/blogs/345822
任务26：华为云数据库之数据库完整性