MySQL数据库技术与应用—数据的完整性—实体完整性、域 ...

黄庆西

MySQL数据库技术与应用—数据的完整性，主要介绍了 MySQL 中保持数据完整性的机制，通过具体的实例为读者讲解了实体完整性、域完整性、引用完整性和索引的概念及其各种策略的使用方法。本文中的知识点需要读者将理论与实践相结合，从而到达更好的学习效果。

数据表是数据存储的基础单元，在从外界向数据表输入数据时可能会由各种原因造成输入数据的无效或者错误。为了防止数据库中存在不符合语义规定的数据或者由错误信息的输入/输出造成无效操作， MySQL 提供多种方法来保证数据的完整性。数据的完整性主要分为实体完整性、域完整性和引用完整性，本章将重点对完整性约束进行讲解。
实体完整性
Ø 主键与主键约束
主键（又称为主关键字）是用于唯一确定表中每一行数据的标识符，是表中某一列或者多列的组合，多个列组成的主键称为复合主键。
主键约束是对主键的约束规则，如下所示。
（ 1）唯一性：每个表中只能存在一个主键，且主键的值能唯一标识表中的每一行，就像每个人的身份证号码是不同的，能唯一标识每一个人。
（ 2）非空性：主键可以由多个字段组成，且不受数据类型的限制。另外，字段所在的列中不能存在空值（ NULL）。
在 MySQL 中使用 PRIMARY KEY 字段来定义数据表中的主键。在创建表时可以为字段添加主键约束，具体的语法格式如下所示。
需要注意的是，主键表示的是一个实体，而主键约束是针对这个实体所设定的规则或属性。为了使读者可以更加形象地理解主键的定义规则，这里将使用一张学生信息表来进行详细介绍，如图所示。


图中， id 字段所对应的值可以明确标识每一行的数据，例如， id=3 对应的是学生王五的基本信息。 id 字段中的值不重复、不为空，因此 id 字段可以设置为主键。
在 MySQL 中使用 PRIMARY KEY 字段来定义数据表中的主键。在创建表时可以为字段添加主键约束，具体的语法格式如下所示。


以上语法格式中， “字段名”表示需要设置为主键的列名， “数据类型”为该列的数据类型，PRIMARY KEY 表示主键。另外，在表创建完成后，也可以为表添加主键约束，具体的语法格式如下所示。


接下来演示设置主键可能会出现的问题。首先创建一张订单表 orders，表结构如表所示。


在表中列出了订单表的字段、字段类型和说明，为了与先前的数据进行隔离，此处将新建一个名为 Chapter_Five 的数据库，具体代码如下所示。


然后根据表中的信息创建 orders 表，具体的 SQL 语句如下所示。


表创建完成后，向其中插入两条 oid 字段相同的数据， SQL 语句如下所示。


此时，将 orders 表中的 oid 字段设置为主键，代码如下所示。


从以上执行结果可以看出，添加主键失败。这是因为数据表中存在两条相同的数据，这与主键约束规则冲突。删除其中一条数据并尝试重新添加主键， SQL 语句和执行过程如下所示。


从上方代码的执行结果可以看出主键设置成功。 为了进一步验证主键约束的规则，在表 orders中插入一个空值（ NULL），具体的 SQL 语句和执行过程如下所示。


从上方代码的执行结果可以看出，向 orders 表插入空值失败，这也证明了数据库中主键不为空的规则。
在实际的开发中，也可以删除表中的主键，具体的语法如下所示。


关于删除主键，此处不再进行演示，读者可以自行操作。

Ø 唯一约束
唯一约束用于限制不受主键约束的列上数据的唯一性，与主键约束不同的是，唯一约束可以为空值且一个表中可以放置多个唯一约束。 MySQL 中可以使用 UNIQUE 关键字添加唯一约束。
在创建表时为某个字段添加唯一约束的具体语法格式如下所示。


以上语法格式中， “字段名”表示需要添加唯一约束的列名， “数据类型”和 UNIQUE 关键字之间需要使用空格隔开。另外，也可以使用 ALTER 命令将唯一约束添加到已经创建完成的表中，具体的语法格式如下所示。


以 orders 表为例，将 phone（收货人电话）字段设置为唯一约束的 SQL 语句及执行过程如下所示。


从以上代码的执行结果可以看出， orders 表中 phone 字段添加唯一约束成功。为了进一步验证唯一约束的规则，分别向表中插入两条不同的数据，具体代码和执行过程如下所示。


从以上代码的执行结果可以看出： 第一条插入命令中phone字段的值与表中phone字段的值相同，导致数据插入失败；第二条插入命令将 phone 字段的值设置为 NULL 后插入成功。
在实际的开发中，也可以删除表中的唯一约束，具体的语法如下所示。


读者也可以使用下面的方式。



Ø 自动增长列
在创建表时，表中的 id 字段的值一般从 1 开始，当需要插入大量的数据时，这种做法不仅比较烦琐，而且还容易出错。为此，可以将 id 字段的值设置为自动增长。 MySQL 中可以使用AUTO_INCREMENT 关键字设置表中字段值的自动增长，在创建表时将某个字段设置为自动增长列的语法格式如下所示。


以上语法格式中， “字段名”表示需要设置字段值自动增长的列名， “数据类型”和AUTO_INCREMENT 关键字之间需要使用空格隔开。另外，也可以将已经创建完成的表的字段设置成自动增长列，具体语法格式如下所示。


以 orders 表为例，将 oid 字段设置为自动增长列的 SQL 语句如下所示。


从上方代码的执行结果可以看出， orders 表中的 oid 字段已经成功设置成自动增长列。
域完整性
域完整性指的是对数据表中单元格的约束，通过给列定义规则来约束单元格的属性。常见的域完整性约束包括数据类型约束、非空约束和默认值约束，其中数据类型约束就是在创建表时所指定的字段类型。本节将重点讲解域完整性中的非空约束和默认值约束。
Ø 非空约束
非空约束用于保证数据表中的某个字段的值不为 NULL，在 MySQL 中可以使用 NOT NULL 关键字为列添加非空约束。在创建表时，为某个字段添加非空约束的具体语法格式如下所示。


以上语法格式中，“字段名”表示需要添加非空约束的列名， “数据类型”和 NOT NULL 关键字之间需要用空格隔开。另外，非空约束也可以添加到已经创建完成的表中，语法格式如下所示。


ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;


从上方代码的执行过程可以看出，已成功为表 orders 中的 addr 字段添加非空约束。

Ø 默认值约束
默认值约束用于为数据表中的某个字段设置默认值，例如，订单的创建时间如果不进行手动填写，则默认为当前时间。在 MySQL 中使用 DEFAULT 关键字设置默认值约束，创建表时，为某个字段添加默认值约束的具体语法格式如下所示。


以上语法格式中，“字段名”表示需要添加默认值约束的列名，“数据类型”和 DEFAULT 关键字之间需要使用空格隔开。另外，默认值约束也可以添加到已经创建完成的表中，语法格式如下所示。


下面具体讲解 MySQL 中为字段设置默认值约束的操作方法。
在表 orders 中增加一列名为 postalcode 的字段，数据类型为 INT，并将其默认值设置为 100000，具体的 SQL 语句和操作流程如下所示。


从上方代码的执行结果可以看出， 在 orders 表中成功添加了 postalcode 字段并将其默认值设置为100000。
引用完整性
引用完整性是对实体之间关系的描述，如果要删除被引用的对象，那么也要删除引用它的所有对象或者把引用值设置为空值，如图所示。


图中， B 和 C 分别引用了 A 中的 a 字段（ A 被 B 和 C 引用），所以 a1 和 a2 受 a 的变化影响。如果要删除 a，那么需要将 a1 和 a2先删除，或者将 a 的值设置为 NULL。
Ø 外键与外键约束
外键的定义是具有相对性的。如果一个字段 x 在一张表中为主键，在另一张表中不为主键，那么字段 x 称为第二张表的外键。外键的主要作用是建立和加强两个表之间的数据连接，如图所示。


在表 A 中的字段 a 被设置为主键的前提下，表 B 中引用了表 A 中的字段 a 的数据，所以表 B 中的字段 a 被称为表 A 的外键。

外键约束是针对外键设定的约束规则，接下来通过两张表来讲解外键约束规则。
首先创建学科表 subject，其中有专业编号 sub_id 和专业名称 sub_name 这 2 个字段，并将 sub_id字段设置为该表的主键。具体的 SQL 语句和执行结果如下所示。


然后创建学生表 student，其中有学生编号 stu_id、学生姓名 stu_name 和专业编号 sub_id 这 3 个字段。具体的 SQL 语句和执行结果如下所示。


需要注意的是，创建的 subject 表中 sub_id 为主键， student 表中的 sub_id 字段是对 subject 表中的主键进行的引用，所以 student 表中的 sub_id 字段就是外键。 sub_id 字段被作为主键的表（ subject）称为主表，而外键所在的表（ student）称为从表（或子表），两表之间是主从关系。 student 表可以通过外键 sub_id 来获得 subject 表中的信息，从而建立两个表中数据的连接。
在创建表时将某字段设置为外键的基本语法格式如下所示。


另外，表创建完成后也可以添加外键，具体语法格式如下所示。


使用相关命令将上面创建的 student 表中的 sub_id 字段设置为外键，具体的 SQL 语句如下所示。


从上方代码的执行结果可以看出外键添加成功。下面根据不同的情况讲解外键所对应的外键约束规则。

（ 1）主表不存在数据时，从表无法插入数据。
尝试在从表 student 中插入数据，具体代码如下所示。


从上方代码的执行结果可以看出添加数据失败，这是因为如果主表中没有数据，那么从表中将无法插入数据。为主表 subject 添加数据，具体代码如下所示。


从以上代码的执行结果可以看出数据插入完成。接下来向从表 student 插入数据，具体代码如下所示。


从执行结果可以看出，向从表插入数据成功。

（ 2）从表的数据删除后才可以删除主表中的数据。
尝试直接删除主表 subject 中的数据，具体代码如下所示。


从上方代码的执行结果可以看出，删除主表中的数据失败。由于主表 subject 中的数据被从表student 引用，因此无法删除主表 subeject 中的数据。此时可以先删除从表中的数据，再删除主表中的数据，具体的 SQL 语句如下所示。


从上面代码的执行过程可以看出，数据删除成功。

Ø 删除外键约束
读者在实际的开发应用中可能会遇到需要解除两个表之间关联的情况，这时就需要删除外键约束，其具体的语法格式如下所示。


下面演示将 student 表中的外键约束删除。首先查看表的外键名。


从以上代码的执行结果可以看出， student 表中的 sub_id 字段为外键，关联的主表是 subject，外键名为 student_ibfk_1。然后删除 student 表中的外键约束，具体操作如下所示。


从以上代码的执行结果可以看出外键删除成功。 为了进一步验证， 使用相关命令再次查看 student表的结构信息。


从以上代码的执行结果可以看出， student 表中的外键删除成功。
索引
如果把数据表比喻成一本书，那么索引就是这本书的目录，它可以帮助用户实现根据目录中的页码快速找到对应的内容。如果不添加索引，数据库每次执行查询操作时，都会从表的头部开始遍历数据，这无疑降低了数据库的运行效率。 MySQL 中的索引分为普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引、组合索引等。接下来将介绍生产环境中常用的普通索引和唯一索引。

Ø 普通索引
普通索引是最基本的索引类型，它主要的作用是加快对数据的访问速度。在创建表时设置普通索引的语法格式如下所示。


另外，对于已经创建完成的表，也可以为表中的某个字段创建普通索引，基本的语法格式如下所示。


以前面创建的 student 表为例，为 stu_id 字段添加索引的 SQL 语句如下所示。


以上语句的执行结果证明为 student 表中的 stu_id 字段创建普通索引成功。为了进一步验证，使用 SHOW INDEX FROM 命令查看表中的索引信息。




从以上执行结果可以看出，为 stu_id 字段成功创建了名为 id 的普通索引。结果列表中各字段的含义如表所示。


需要注意， 在表中如果将某字段设置为主键或外键， 那么 MySQL 会自动为该字段设置一个索引。从上方代码的执行结果也可以看出，在表中分别创建了名为 stu_id 和 sub_id 的索引。在实际的应用场景中，也可以将普通索引删除，具体的 SQL 语句格式如下所示。


尝试将 student 表中名为 stu_id 的索引删除，具体的执行语句如下所示。


使用 SHOW INDEX FROM 命令查看 student 表中的索引。


从上方代码的执行结果可以看出， student 表中名为 stu_id 的索引被删除。另外，如果需要删除索引名为 PRIMARY 的主键索引，可以使用以下语句格式。


删除主键索引的操作此处不再赘述。

Ø 唯一索引
MySQL 中使用 UNIQUE 关键字为某列定义唯一索引，被定义唯一索引的列不允许存在重复的值。当有新的数据插入时， MySQL 会自动检查新记录中的字段值是否已经存在于数据表中，如果已经存在，系统将拒绝这条数据的插入。唯一索引不仅可以保证数据的唯一性，而且可以简化 MySQL对索引的管理工作，使索引变得简单高效。
在创建表时可以创建唯一索引，语法格式如下所示。


上方语法格式中， UNIQUE INDEX 关键字表示唯一索引， “索引名”为可选项，括号中的“字段名”表示创建索引的字段，“长度”为可选项。另外，对于已经创建完成的表，也可以为某个字段创建唯一索引，语法格式如下所示。


以本章中创建的 subject 表为例，为 sub_name 字段添加唯一索引的 SQL 语句如下所示。


以上语句的执行结果证明为 subject 表中的 sub_name 字段创建唯一索引成功。为了进一步验证，使用 SHOW CREATE TABLE 命令查看表的具体信息。




从以上结果可以看出，为 sub_name 字段成功创建了唯一索引。在实际的开发中，也可以使用ALTER 语句删除表中的唯一索引，具体的语法格式如下所示。


下面演示使用 ALTER 语句删除唯一索引。


从上方代码的执行结果可以看出，名为 sub_name 的唯一索引被成功删除。

Ø 索引过多引发的问题
在 MySQL 中合理地创建索引不仅可以极大地提高在数据库中获取所需信息的速度， 而且能提高服务器处理相关搜索请求的效率。建立索引的优点如下。
（ 1）可以加快数据的检索速度。
（ 2）可以保证数据库表中每一行数据的唯一性。
（ 3）加速表和表之间的连接。
（ 4）在使用分组或者排序子句时，可以减少查询中分组和排序的时间。
虽然索引在检索效率上具有诸多的积极作用，但过多的索引也会引起一些不必要的问题。
（ 1）在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
（ 2）过多的索引需要占大量的物理空间。
（ 3）当对数据进行增、删、改操作时，用户也需要对索引进行相应的维护，降低了数据的维护速度。

钟汉良

鼎力支持！！