[大数据]数据库原理及MySQL应用 | 约束

?约束是保证数据完整性的一种数据库对象，按约束作用不同，分为七种。

约束从字面上来看就是受到限制，它是附加在表上，通过限制列中、行中、表之间数据来保证数据完整性的一种数据库对象。

在MySQL中，有多种约束，可按以下不同方式进行分类。

● 按约束的应用范围不同，约束可分为列级约束和表级约束。列级约束是数据表中列定义的一部分，只能作用于表中的一列；表级约束独立于列定义之外，作用于表中的多列。当一个约束中必须包含多个列时，必须使用表级约束。

● 按约束的作用不同，约束可分为主键约束(PRIMARY KEY)、唯一性约束(UNIQUE)、外键约束(FOREIGN KEY)、默认值约束(DEFAULT)、非空约束(NOT NULL)、自增特性(AUTO_INCREMENT)和检查约束(CHECK)。

提示/

给约束定义的名称，称之为约束名。约束名可以由用户自己指定，也可由系统指定。对于约束名的命名推荐为type_table_column，其中type表示约束的类型，table为表名，column为列名，例如：PK_books_bookID表示在图书表books的bookID列上创建了主键约束。

约束创建的时机，分为以下两种。

(1) 在建表的同时创建约束。

(2) 建表后(修改表)创建约束。

01、主键约束

主键约束(PRIMARY KEY)是在表中定义一个主键来唯一确定表中的每一行记录。主键可以定义在单列上，也可以定义在多列上。该约束通过主键索引来强制实体完整性。

主键约束具有以下特点。

每个表最多只能定义一个主键约束，外键约束使用它作为维护数据完整性的参考点。

● 主键约束所在列不允许输入重复值。如果主键约束由两个或两个以上的列组成，则该组合的取值不重复。

● 在主键约束中定义的所有列都必须定义为非空(NOT NULL)。

● 主键约束名总为PRIMARY，所以不需要指定约束名。

● 主键约束在指定的列上创建了一个主键索引，索引名默认为PRIMARY。

● 关系模型理论要求为每个表定义一个主键，但MySQL并没有这样的要求，可以创建一个没有主键的表，但是从安全角度考虑应该为每个表指定一个主键。

当在一个已经存放了数据的表上增加主键约束时，MySQL会自动对表中的数据进行检查，以确保这些数据能够满足主键约束的要求，即设定主键约束的列的所有数据值必须唯一，否则系统会返回错误信息，并拒绝执行增加约束的操作。

主键约束的基本语法格式如下所示。

?语法说明如下。

• 如果定义的是列级约束，则不需要指定列名column，只需在列定义的后面加上PRIMARY KEY。

• 如果定义的是表级约束，则需要指定主键所在列名，在表定义语句后，加上该子句。

提示/

创建主键约束时系统会自动创建一个主键索引，该索引不同于手工创建的索引，不能使用DROP INDEX语句直接删除，只有删除主键约束，才能删除其相应的索引。

如果有外键约束正在参考主键约束中的数据，那么这些主键约束中的数据便不能被修改，也不能被删除。但是，如果在创建外键约束时，指定了级联操作子句，就可以修改或删除主键约束中的数据了。

1. 创建数据表时添加主键约束

在创建数据表时可以将一列或多列的组合设置为主键约束，该约束由系统提供主键约束名PRIMARY，因此即便人工设置约束名的命令可以成功运行，但系统仍然将PRIMARY作为主键约束名。

【例6-25】在图书销售数据库booksale中创建图书表books2，其中将bookid列设置为主键，然后查看约束信息及索引情况。

可以从information_schema架构下的系统表查看约束。其中CONSTRAINT_NAME列为约束名，CONSTRAINT_TYPE列为约束类型。创建books2表的同时创建了一个主键约束，这个主键约束是一个列级约束，默认的主键约束名为PRIMARY，约束类型为PRIMARY KEY。创建该主键约束的同时还创建了一个主键索引，索引和约束同名为PRIMARY，索引关键字是bookid。

输入以下数据进行验证。

?数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 1062-Duplicate entry '1' for key 'books2.PRIMARY'。因为bookid的值“1”是重复的，违反了主键约束。

【例6-26】在图书销售数据库booksale中创建订单项目表orderitems2，该表的主键约束设置在orderid和bookid两列上，然后查看约束信息及索引情况。

创建orderitems2表的同时创建了一个主键约束，这个主键约束是一个表级约束，默认的主键约束名为PRIMARY，约束类型为PRIMARY KEY。创建该主键约束的同时还创建了一个主键索引，索引和约束同名为PRIMARY，索引第一关键字是orderid，第二关键字是bookid。

输入以下数据进行验证。

插入成功，因为主键建立在orderid和bookid两列上，因此只有orderid列值相同或bookid列值相同不违反主键约束，只有orderid和bookid两列上的值都重复时，才会违反主键约束，导致插入失败。

2. 修改数据表时添加主键约束

如果创建数据表时没有指定主键约束，可以在修改数据表时设置主键约束。

【例6-27】在图书销售数据库booksale的表categories中，为ctgcode列添加主键约束。

系统默认的主键约束名为PRIMARY。若categories表的ctgcode列所有取值均唯一，则主键约束可以成功建立，否则将会创建失败。

提示/

在修改表时添加主键需要注意，由于表中已经存在数据，若设置主键的列存在违反实体完整性的情况，则主键约束无法创建成功。

3. 删除主键约束

一个表只允许有一个主键约束，对于已存在的主键约束，可以修改或删除它。例如：要将其他列或列组合设为主键约束，必须先删除现有的主键约束，然后再重新创建。

【例6-28】在图书销售数据库booksale的表categories中，删除现有的主键约束。

主键约束删除的同时，自动生成的主键索引也同步删除。

02、唯一性约束

唯一性约束(UNIQUE)是用来保证数据表中的一列或多列中的数据是唯一的。该约束通过唯一性索引来强制实体完整性。当表中已经存在主键约束时，如果需要在其他列上实现实体完整性，由于一个表中只能有一个主键约束，因此可以通过创建唯一性约束来实现。

当在一个已经存放了数据的表上增加唯一性约束时，MySQL会自动对表中的数据进行检查，以确保这些数据能够满足唯一性约束的要求，即设定唯一性约束的列除NULL外，所有数据的值必须唯一，否则系统会返回错误信息，并拒绝执行增加约束的操作。

唯一性约束具有以下特点。

● 每个表可以定义多个唯一性约束，且多个唯一性约束的列可以重合。

● 唯一性约束所在列不允许输入重复值。如果唯一性约束由两个或两个以上的列组成，则该组合的取值不重复。

● 唯一性约束所在列允许取空值，但必须用NULL声明。不过，当和参与唯一性约束的任何值一起使用时，每列只允许一个空值。

● 唯一性约束在指定的列上创建了一个唯一性索引。?

提示/

主键约束和唯一性约束的区别是：一个表只允许建立一个主键约束，而唯一性约束可以建立多个；主键约束的关键列不允许取空值，而唯一性约束的关键列允许取空值；主键约束默认创建的是主键索引，唯一性约束默认创建的是唯一性索引。

唯一性约束的基本语法格式如下所示。

语法说明如下。

• constraint_name是可选选项，用于指定约束的名称。如果用户没有提供约束名称，系统将会自动生成一个以被约束列名命名的约束名称。约束名称最大长度为64个字符，而且区分大小写。

• UNIQUE是定义唯一性约束的命令关键字。

• 如果定义的是列级约束，且不需要指定约束名，则不需要指定列名column，只需在列定义的后面加上UNIQUE。

• 如果定义的是表级约束，或是列级约束但要指定约束名，则需要指定唯一性约束所在列名，在表定义语句后，加上该子句。

1. 创建数据表时添加唯一性约束

在创建数据表时可以为一列或多列的组合设置唯一性约束，该约束可由系统提供唯一性约束名，也可由用户指定唯一性约束名。

【例6-29】在图书销售数据库booksale中创建图书类别表categories2，为ctgcode列创建唯一性约束，然后查看约束信息及索引情况。

创建categories2表的同时创建了一个唯一性约束，这个唯一性约束是一个列级约束，默认的约束名为被约束列名ctgcode，约束类型为UNIQUE。创建该唯一性约束的同时还创建了一个唯一性索引，索引和约束同名为ctgcode，索引关键字是ctgcode。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：

因为ctgcode的值'computer'是重复的，违反了唯一性约束。

【例6-30】在图书销售数据库booksale中创建顾客表customers2，为emailaddress列设置唯一性约束，约束名为UN_customers2_emailaddress，然后查看约束信息及索引情况。

创建customers2表的同时创建了一个主键约束和一个唯一性约束，这两个约束都是列级约束，主键约束名为系统默认的约束名PRIMARY，约束类型为PRIMARY KEY，唯一性约束名为指定的约束名UN_customers2_emailaddress，约束类型为UNIQUE。创建该主键约束的同时还创建了一个主键索引，索引和约束同名为PRIMARY，索引关键字是cstid。创建该唯一性约束的同时还创建了一个唯一性索引，索引和约束同名为UN_customers2_emailaddress，索引关键字是emailaddress。

2. 修改数据表时添加唯一性约束

如果创建数据表时没有指定唯一性约束，可以在修改数据表时设置唯一性约束。

【例6-31】在图书销售数据库booksale的表books2中，为isbn列设置唯一性约束。

默认的约束名为被约束列名isbn。若books2表的isbn列所有取值均唯一，则唯一性约束可以成功建立，否则将会创建失败。

提示/

在修改表时添加唯一性约束需要注意，由于表中已经存在数据，若设置唯一性约束的列存在违反实体完整性的情况，则唯一性约束无法创建成功。

3. 删除唯一性约束

一个表可以含有多个唯一性约束，对于已存在的唯一性约束，可以修改或删除它。若要修改唯一性约束，必须先删除现有的唯一性约束，然后再重新创建。

【例6-32】在图书销售数据库booksale的表books2中，删除唯一性约束。

唯一性约束删除的同时，自动生成的唯一性索引也同步删除。

03、外键约束

外键约束(FOREIGN KEY)是指用于建立和加强两个表之间的连接的一列或多列，即在某一列或多列的组合上定义外键约束，这些列值参考某个表中的主键约束列。该约束强制参考完整性。

定义主键约束的表称之为主键表或父表，定义外键约束的表称之为外键表或子表，外键表的被约束列的取值必须是主键表的被约束列的值或为空。

外键约束具有以下特点。

● 每个表可以定义多个外键约束。

● 临时表不能创建外键约束。

● 外键表中被约束的列必须和主键表中被约束的列数据类型一致、长度一致。

● 外键约束将自动创建索引。

● 外键约束的主要目的是控制可以存储在外键表中的数据，但它还可以控制对主键表中数据的更改。

● 根据参照动作，可以控件父表数据的删除。

外键约束的基本语法格式如下所示。

语法说明如下。

●constraint_name是可选选项，用于指定约束的名称。如果用户没有提供约束名称，系统将会自动生成一个以表名开头、加上“_ibfk_”以及一个数字编号(1，2，3，…)组成的约束名字。约束名称最大长度为64个字符，而且区分大小写。

● FOREIGN KEY是定义外键约束的命令关键字。

● REFERENCES用于指定该外键参考哪个父表中的哪个主键列或候选键列。

● ON DELETE和ON UPDATE选项是通过使用级联参照完整性约束，定义当用户试图删除或更新现有外键指向的键时，数据库引擎将执行以下操作。

◇ RESTRICT表示拒绝对父表进行删除或更新操作。

◇ CASCADE表示如果在父表中删除或更新了一行，则将在引用表中删除或更新相应的行，即级联删除或级联更新。如果timestamp列是外键或被引用键的一部分，则不能指定CASCADE。

◇ SET NULL表示如果删除或更新了父表中的相应行，则会将构成外键的所有值设置为NULL。若要执行此约束，外键列必须可为空值。

◇ NO ACTION与RESTRICT的作用相同，它是标准的SQL关键字。

◇ SET DEFAULT表示如果删除或更新了父表中的相应行，则会将构成外键的所有值设置为它们的默认值。若要执行此约束，外键列必须具有默认值定义。如果某个列可为空值，并且未设置显式的默认值，则会使用NULL作为该列的隐式默认值。

1. 创建数据表时添加外键约束

在创建数据表时可以添加外键约束。

【例6-33】在图书销售数据库booksale中创建评论表comments2，为cstid列添加外键约束，该列的取值要参考customers表中的cstid列，为bookid列添加外键约束，该列的取值要参考books表中的bookid列，该约束名为FK_books_comments2_bookid，然后查看约束和索引的情况。

注意/

必须先创建父表books，且books表的bookid列是主键，再创建子表comments2。

对外键约束来说，虽然支持列级约束的创建语法，但实际上没有效果，因此在cstid列上创建外键约束的写法无法成功地创建外键约束，而在bookid列上创建外键约束的写法可以成功地创建外键约束。因此在创建comments2表的同时创建了一个外键约束，这个外键约束的约束名为指定约束名FK_books_comments2_bookid，约束类型为FOREIGN KEY。

创建该外键约束的同时还创建了一个索引，索引名同约束名为FK_books_comments2_bookid，索引关键字是bookid。

2. 修改数据表时添加外键约束

如果创建数据表时没有指定外键约束，可以在修改数据表时设置外键约束。如果已经创建了外键约束，但是没有加上级联功能，则需要先将该外键约束删除，然后重新建立外键约束时添加级联功能。

【例6-34】在图书销售数据库booksale的表comments2中，为cstid列添加外键约束，该列的取值要参考customers表中的cstid列。如果删除或更新了customers表中的一条记录，则 comments2表相关的记录也相应删除或更新，然后查看约束和索引的情况。

命令中未指定约束名，因此约束名由系统自动生成，名为"comments2_ibfk_1"。创建该外键约束的同时还创建了一个索引，当不指定约束名时自动生成的索引名为外键约束的列名cstid，索引关键字是cstid。

3. 删除外键约束

一个表可以含有多个外键约束，对于已存在的外键约束，可以修改或删除它。若要修改外键约束，必须先删除现有的外键约束，然后再重新创建。

【例6-35】在图书销售数据库booksale的表comments2中，删除约束名为"comments2_ibfk_1"的外键约束。查看约束和索引的情况。

指定的外键约束已经删除，但外键约束创建时生成的索引不会自动删除。

?

04、默认值约束

默认值约束(DEFAULT)通过设置默认值来强制域完整性。在表中的某个列上定义了默认约束后，当插入新的数据行时，如果没有为该列指定数据，则系统将默认值赋值给该列。

默认值约束具有以下特点。

● 表中的每个列上只能定义一个默认约束。

● 默认值只能是常量值和CURRENT_TIMESTAMP(返回当前的日期和时间)。

● 默认值不能参照于其他列或其他表的值。

默认值约束的基本语法格式如下所示。

语法说明如下。

• SET是可选选项，当向已有的表中添加默认值约束时使用的命令关键字；新建表中添加默认值约束时不用该关键字。

• DEFAULT是定义默认值约束的命令关键字。

• constant_expression是默认值的常量表达式。此表达式若为文本字符串，请用单引号(‘)将值括起来。

• 该约束只能为列级约束，只需在列定义的后面加上该子句。

1. 创建数据表时添加默认值约束

在创建数据表时可以为指定列设置默认值约束。

【例6-36】在图书销售数据库booksale中创建评论表comments3，为comment列添加一个默认值约束，默认值设为good。

输入以下数据进行验证。

第一条语句的comment列输入的空字符串(' ')，所以显示为空白；第二条语句的comment列没有输入值，但是显示记录中该列的值为good，说明该记录自动使用了comment列的默认值。

【例6-37】在图书销售数据库booksale中创建订单表orders2，为orderdate列添加一个默认值约束，默认值设为创建时间。

默认值只能是常量值和CURRENT_TIMESTAMP，且CURRENT_TIMESTAMP只适合timestamp数据类型。创建orders2表的同时创建了两个默认值约束，orderdate列设置默认值为CURRENT_TIMESTAMP，shipdate列设置默认值为空。

输入以下数据进行验证。

order2表中，orderdate列显示当前系统日期，而shipdate列为空。

2. 修改数据表时添加默认值约束

如果创建数据表时没有指定默认值约束，可以在修改数据表时设置默认值约束。

【例6-38】在图书销售数据库booksale的表customers表中，为password列添加一个默认值约束，默认值设为“12345678”，然后查看表结构。

3. 删除默认值约束

一个表可以含有多个默认值约束，对于已存在的默认值约束，可以修改或删除它。若要修改默认值约束，必须先删除现有的默认值约束，然后再重新创建。

【例6-39】在图书销售数据库booksale的表comments3表中，删除建立在comment列上的默认值约束。

05、非空约束

非空约束(NOT NULL)将保证所有记录中该列都有值。在表中的某列上定义了非空约束后，当插入新数据行时，如果没有为该列指定数据，则数据库系统会报错。

非空约束具有以下特点。

● 列级约束，只能使用列级约束语法定义。

● 确保列值不允许为空。

提示/

?所有数据类型的值都可以是NULL值；空字符串不等于NULL，0也不等于NULL。

非空约束的基本语法格式如下所示。

?

语法说明如下。

• NOT NULL是设置非空约束的命令关键字。

• 该约束只能为列级约束，只需在列定义的后面直接添加该关键字，不添加该关键字时默认为NULL。

1. 创建数据表时添加非空约束

在创建数据表时可以为指定列设置非空约束。

【例6-40】在图书销售数据库booksale中创建图书类别表categories3，为ctgcode列创建非空约束。

输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 1048-Column 'ctgcode' cannot be null。因为ctgcode列的值是NULL，违反了非空约束。

2. 修改数据表时添加非空约束

如果创建数据表时没有指定非空约束，可以在修改数据表时设置非空约束。

【例6-41】在图书销售数据库booksale的表categories3中，为ctgname列创建非空约束。

该方法既可改变列的数据类型，又可为列添加非空约束。

3. 删除非空约束

删除非空约束的方法其实就是修改数据表，为列设置属性NULL。

【例6-42】在图书销售数据库booksale的表categories3中，删除ctgname列上的非空约束。

06、自增约束

自增约束(AUTO_INCREMENT)是MySQL数据库中一个特殊的约束，其主要用于为表中插入的新记录自动生成唯一的ID。

自增约束具有以下特点。

● 一个表只能有一个列使用自增约束，且该列必须是主键或主键的一部分。

● 自增列必须具备NOT NULL属性。

● 自增约束的列可以是任何整数类型(TINYINT、SMALLINT、INT、BIGINT等)。

● 默认情况下自增列中的第一个值是1，后续值自动加1。如果用户设置了一个非1的初始值，后续值将在该值基础上自动加1。

● 自增数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127，若加上UNSIGNED，则最大为255。一旦达到上限，自增就会失效。

自增约束的基本语法格式如下所示。

语法说明如下。

• AUTO_INCREMENT是设置自增约束的命令关键字，在列的后面直接添加该关键字。

• AUTO_INCREMENT=n是可选选项，用于设置自增的初始值，设置在表结构的外面。省略时表示从1开始自增。

?

1. 创建数据表时添加自增约束

在创建数据表时可以为主键或主键的部分列设置自增约束。

【例6-43】在图书销售数据库booksale中创建评论表comments4，为cmmid列添加主键约束和自增约束，设置自增的初始值为100。

cmmid列的自动增长值为100、101、102等，以100为起始值间隔为1这样增长。

2. 修改数据表时添加自增约束

如果创建数据表时没有指定自增约束，可以在修改数据表时设置自增约束。

【例6-44】在图书销售数据库booksale的表books2中，为bookid列创建自增约束。

前提是bookid列是主键或主键的一部分。

3. 删除自增约束

删除自增约束的方法其实就是修改数据表，去掉AUTO_INCREMENT。

【例6-45】在图书销售数据库booksale的表books2中，删除bookid列上的自增约束。

07、检查约束

检查约束(CHECK)是用来验证用户输入某一列的数据的有效性。该约束通过列中的值来强制域的完整性，它用来指定某列可取值的集合或范围。

检查约束具有以下特点。

● 每个表可以定义多个检查约束。

● 检查约束可以参考本表中的其他列。例如：在订单表orders中，shipdate(发货日期)列可以引用orderdate(订购日期)列，使得shipdate列的数据大于orderdate列的数据。

● 检查约束不能放在AUTO_INCREMENT属性的列上或数据类型为timestamp的列上，因为这两种列都是自动插入数据的。

● 当向设有检查约束的表中插入记录或更新记录时，该记录中的被约束列的值必须满足检查约束条件，否则无法录入。

● 可以为列级完整性约束，也可以为表级完整性约束。

● 检查约束在MySQL 8.0.16版本中才实现了自动对写入的数据进行约束检查。

检查约束的基本语法格式如下所示。

语法说明如下。

• constraint_name是可选选项，用于指定约束的名称。如果用户没有提供约束名称，系统将会自动生成一个以表名开头、加上"_chk_"以及一个数字编号(1，2，3，…)组成的约束名字。约束名称最大长度为64个字符，而且区分大小写。

• CHECK是定义检查约束的命令关键字。

• expr是一个布尔表达式，用于指定约束的条件。表中的每行数据都必须满足expr的结果为TRUE或UNKNOWN(NULL)。如果表达式的结果为FALSE，将会违反约束。

• ENFORCED是可选的子句，用于指定是否强制该约束：如果忽略或指定了ENFORCED，创建并强制该约束；如果指定了NOT ENFORCED，创建但是不强制该约束，这也意味着约束不会生效。

1. 创建数据表时添加检查约束

在创建数据表时可以添加检查约束。

?

【例6-46】在图书销售数据库booksale中创建图书表books3，其中unitprice列的取值范围在0～200元，ctgcode列的取值只能是computer和language。

创建books3表的同时创建了一个主键约束和两个检查约束，unitprice列上的检查约束是一个列级约束，默认的约束名为books3_chk_1，约束类型为CHECK；ctgcode列上的检查约束是一个表级约束，默认的约束名为books3_chk_2，约束类型为CHECK。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 3819-Check constraint 'books3_chk_1' is violated.。因为unitprice列的值249.00不满足检查约束的表达式，违反了检查约束。

2. 修改数据表时添加检查约束

如果创建数据表时没有指定检查约束，可以在修改数据表时设置检查约束。

【例6-47】在图书销售数据库booksale的表orders2中，约定发货日期shipdate要在订购日期orderdate之后，因此为这两列设置检查约束。

若books2表的shipdate列取值均大于或等于orderdate列的取值，则检查约束可以成功建立，否则将会创建失败。

输入以下数据进行验证。

数据正常插入，再输入以下数据进行验证。

插入失败，提示错误信息：［Err］ 3819 - Check constraint 'orders2_chk_1' is violated.。因为shipdate列的值"2021-04-14"小于orderdate列的值"2021-04-15"，不满足检查约束的表达式，违反了检查约束。

3. 删除检查约束

一个表可以含有多个检查约束，对于已存在的检查约束，可以修改或删除它。若要修改检查约束，必须先删除现有的检查约束，然后再重新创建。

【例6-48】在图书销售数据库booksale的表books3中，删除建立在ctgcode列上的检查约束。

?

?