分享到

什么是 MySQL 列和表约束?

约束是用户定义的要求,用于定义列或表的有效值。您可以将它们视为额外的限制,以便比数据类型允许的范围更严格地限定可接受的值。

约束允许您定义所有条目必须具备的特性,由服务器本身在数据输入或更新时强制执行这些限制。例如,表示各种物质沸点的列值低于其冰点可能不合理。约束可以强制执行这种类型的要求,即使类型无法做到这一点。

约束的定义位置:列约束与表约束

MySQL 允许您创建与特定列或整个表相关的约束。

几乎所有约束都可以在两种形式下使用,无需修改。

约束
CHECK
NOT NULL*
UNIQUE
PRIMARY KEY
FOREIGN KEY

*: NOT NULL 不能用作表约束。但是,您可以通过在 CHECK 表约束中将 IS NOT NULL 作为语句来近似实现相同的结果。

让我们看看列约束和表约束有何不同。

列约束

列约束是附加到单个的约束。它们用于确定列的建议值是否有效。列约束在输入根据基本类型要求(例如确保 int 列的值是整数)进行验证后进行评估。

列约束非常适合表达仅限于单个字段的要求。它们将约束条件直接附加到所涉及的列。例如,我们可以在 person 表中通过在列名和数据类型后添加约束来建模 age 限制:

CREATE TABLE person (
. . .
age INT CHECK (age >= 0),
. . .
);

此代码片段定义了一个 person 表,其中一个列是名为 ageint 类型。 age 必须大于或等于零。列约束易于理解,因为它们作为附加要求添加到它们所影响的列上。

表约束

另一种约束称为表约束约束可以表达几乎所有列约束可以表达的限制,此外还可以表达涉及多个列的限制。表约束不是附加到特定列,而是作为表的独立组件进行定义,并且可以引用表的任何列。

我们之前看到的列约束可以表示为表约束,如下所示:

CREATE TABLE person (
. . .
age INT,
. . .
CHECK (age >= 0)
);

使用了相同的基础语法,但约束是单独列出的。为了利用表约束引入复合限制的能力,我们可以使用逻辑 AND 运算符来连接来自不同列的多个条件。

例如,在银行数据库中,一个名为 qualified_borrowers 的表可能需要检查个人是否拥有现有账户以及提供抵押品的能力,以获得贷款资格。将这两者包含在同一检查中可能更有意义:

CREATE TABLE qualified_borrowers (
. . .
account_number INT,
acceptable_collateral BOOLEAN,
. . .
CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

在这里,我们再次使用 CHECK 约束来检查 account_number 是否不为空,以及贷款员是否通过检查 acceptable_collateral 列将客户标记为具有可接受的抵押品。由于需要检查多个列,因此表约束是必需的。

现在是时候提一下,尽管我们主要在这些示例中使用 CREATE TABLE SQL 命令来创建新表,但您也可以使用 ALTER TABLE 将约束添加到现有表中。当使用 ALTER TABLE 时,新约束会导致表中当前的值根据新约束进行检查。如果值违反约束,则无法添加该约束。

创建约束名称

默认约束名称

当您使用上述语法创建约束时,MySQL 会自动选择一个合理但模糊的名称。对于上面提到的 qualified_borrowers 表,MySQL 会将约束命名为 qualified_borrowers_chk_1

INSERT INTO qualified_borrowers VALUES (123, false);
ERROR 3819 (HY000): Check constraint 'qualified_borrowers_chk_1' is violated.

当约束被违反时,此名称会为您提供有关表和约束类型的信息。然而,在表中存在多个约束的情况下,更具描述性的名称有助于故障排除。

自定义约束名称

您可以选择通过在约束定义前加上 CONSTRAINT 关键字和名称来指定约束的名称。

添加自定义名称的基本语法如下:

CONSTRAINT <constraint_name> <constraint_type_and_details>

例如,如果您想将 qualified_borrowers 表中的约束命名为 loan_worthiness,您可以这样定义表:

CREATE TABLE qualified_borrowers (
. . .
account_number INT,
acceptable_collateral BOOLEAN,
. . .
CONSTRAINT loan_worthiness CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

现在,当我们违反约束时,我们会得到更具描述性的标签:

INSERT INTO qualified_borrowers VALUES (123, false);
ERROR 3819 (HY000): Check constraint 'loan_worthiness' is violated.

您也可以用同样的方式命名列约束。

CREATE TABLE teenagers (
. . .
age INT CONSTRAINT is_teenager CHECK (age >= 13 AND age <= 19),
. . .
);

MySQL 可用约束列表

现在我们已经了解了一些约束的工作原理,我们可以更深入地了解有哪些可用的约束以及它们如何使用。

Check 约束

Check 约束是一种通用约束,允许您指定涉及列或表值并评估为布尔值的表达式。

您之前已经看到了一些 check 约束的示例。Check 约束以关键字 CHECK 开头,然后提供一个括在括号中的表达式。对于列约束,它放在数据类型声明之后。对于表约束,它们可以放在与它们交互的列定义之后的任何位置。

例如,我们可以创建一个 film_nominations 表,其中包含已提名并符合 2019 年长片奖资格的影片:

CREATE TABLE film_nominations (
title VARCHAR(250),
director VARCHAR(250),
release_date DATE CHECK ('2019-01-01' <= release_date AND release_date <= '2019-12-31'),
length INT,
votes INT,
CHECK (votes >= 10 AND length >= 40)
);

我们有一个列检查约束,用于检查 release_date 是否在 2019 年内。之后,我们有一个表检查约束,确保影片获得足够的票数才能被提名,并且长度符合“长片”类别。

在评估 check 约束时,可接受的值将评估为 true。如果新记录的值满足所有类型要求和约束,则该记录将被添加到表中。

INSERT INTO film_nominations VALUES (
'A great film',
'Talented director',
'2019-07-16',
117,
45
);
Query OK, 1 row affected (0.01 sec)

评估为 false 的值会产生错误,指示约束未满足。

INSERT INTO film_nominations VALUES (
'A poor film',
'Misguided director',
'2019-10-24',
128,
1
);
ERROR 3819 (HY000): Check constraint 'film_nominations_chk_2' is violated.

在这种情况下,影片满足了所有条件,除了所需的票数。MySQL 拒绝了提交,因为它未能通过最终的表检查约束。

非空约束

NOT NULL 约束更加集中。它保证列中的值不为空。虽然这是一个简单的约束,但它被频繁使用。

如何在 MySQL 中添加 NOT NULL 约束

要将列标记为要求非空值,请在类型声明后添加 NOT NULL

CREATE TABLE national_capitals (
country VARCHAR(250) NOT NULL,
capital VARCHAR(250) NOT NULL
);

在上面的示例中,我们有一个简单的两列表,将国家映射到其首都。由于这两者都是必填字段,留空没有意义,因此我们添加了 NOT NULL 约束。

现在插入空值会导致错误:

INSERT INTO national_capitals VALUES (
NULL,
'London',
);
ERROR 1048 (23000): Column 'country' cannot be null

NOT NULL 约束仅作为列约束起作用(不能用作表约束)。但是,您可以通过在表 CHECK 约束中使用 IS NOT NULL 来轻松解决此问题。

例如,这通过表约束提供了等效的保证:

CREATE TABLE national_capitals (
country VARCHAR(250),
capital VARCHAR(250),
CHECK (country IS NOT NULL AND capital IS NOT NULL)
);

唯一约束

UNIQUE 约束告诉 MySQL,列中的每个值都不得重复。这在许多不同的场景中都很有用,在这些场景中,多个记录中拥有相同的值是不可行的。

例如,任何处理 ID 的列,根据定义,都应该具有唯一值。如果社会安全号码、学生或客户 ID,或产品 UPC(条形码编号)无法区分特定的人或物品,那么它们将毫无用处。

可以在列级别指定 UNIQUE 约束:

CREATE TABLE supplies (
supply_id INT UNIQUE,
name VARCHAR(250),
inventory INT
);

它们也可以指定为表约束:

CREATE TABLE supplies (
supply_id INT,
name VARCHAR(250),
inventory INT,
UNIQUE (supply_id)
);

使用 UNIQUE 表约束的优点之一是它允许您对列组合执行唯一性检查。这通过指定 MySQL 应该一起评估的两个或多个列来工作。单个列中的值可能会重复,但指定的值组合必须是唯一的。

例如,让我们回顾一下我们之前使用的 national_capitals 表:

CREATE TABLE national_capitals (
country VARCHAR(250) NOT NULL,
capital VARCHAR(250) NOT NULL,
);

如果我们想确保我们不会为同一对添加多个记录,我们可以在这里向列添加 UNIQUE 约束:

CREATE TABLE national_capitals (
country VARCHAR(250) NOT NULL UNIQUE,
capital VARCHAR(250) NOT NULL UNIQUE
);

这将确保国家和首都只在每个表中出现一次。然而,一些国家有多个首都。这意味着我们可能会有多个具有相同 country 值的条目。这与当前设计不符。

INSERT INTO national_capitals VALUES (
'Bolivia',
'Sucre'
);
INSERT INTO national_capitals VALUES (
'Bolivia',
'La Paz'
);
ERROR 1062 (23000): Duplicate entry 'Bolivia' for key 'national_capitals.country'

如果我们仍然希望确保不出现重复条目,同时允许单个列中出现重复值,那么对 countrycapital 的组合进行唯一性检查就足够了:

CREATE TABLE national_capitals (
country VARCHAR(250),
capital VARCHAR(250),
UNIQUE (country, capital)
);

现在,我们可以将玻利维亚的两个首都添加到表中,而不会出现错误:

INSERT INTO national_capitals VALUES (
'Bolivia',
'Sucre'
);
INSERT INTO national_capitals VALUES (
'Bolivia',
'La Paz'
);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

然而,尝试两次添加相同的组合仍然会被约束捕获:

INSERT INTO national_capitals VALUES (
'Bolivia',
'Sucre'
);
INSERT INTO national_capitals VALUES (
'Bolivia',
'Sucre'
);
Query OK, 1 row affected (0.00 sec)
ERROR 1062 (23000): Duplicate entry 'Bolivia-Sucre' for key 'national_capitals.country'

主键约束

PRIMARY KEY 约束具有特殊用途。它表明该列可以用于唯一标识表中的记录。这意味着它必须可靠地唯一,并且每条记录在该列中都必须有一个值。

建议每个表都有主键,但并非必需,并且每个表只能有一个主键。主键主要用于标识、检索、修改或删除表中的单个记录。它们允许用户和管理员使用 MySQL 保证只匹配一条记录的标识符来执行操作。

我们以之前看到的 supplies 表为例:

CREATE TABLE supplies (
supply_id INT UNIQUE,
name VARCHAR(250),
inventory INT
);

这里我们已经确定 supply_id 应该是唯一的。如果我们要将此列用作主键(保证唯一性和非空值),我们可以简单地将 UNIQUE 约束更改为 PRIMARY KEY

CREATE TABLE supplies (
supply_id INT PRIMARY KEY,
name VARCHAR(250),
inventory INT
);

这样,如果我们需要更新特定供应的库存数量,我们可以使用主键来定位它:

INSERT INTO supplies VALUES (
38,
'nails',
5
);
UPDATE supplies set inventory = 10 WHERE supply_id = 38;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

虽然许多表使用单个列作为主键,但也可以使用一组列创建主键,作为表约束。

national_capitals 表是演示这一点的良好候选。如果我们要使用现有列创建主键,我们可以将 UNIQUE 表约束替换为 PRIMARY KEY

CREATE TABLE national_capitals (
country VARCHAR(250),
capital VARCHAR(250),
PRIMARY KEY (country, capital)
);

外键约束

外键是表中的列,它们引用另一个表中的列值。这在各种表中包含相关数据的场景中是可取的,而且通常是必需的。数据库能够轻松连接和引用存储在单独表中的数据是关系型数据库的主要特性之一。

例如,您可能有一个 orders 表来跟踪单个订单,以及一个 customers 表来跟踪客户的联系信息和详细信息。将这些信息分开存放是合理的,因为一个客户可能有很多订单。然而,能够轻松地将这两个表中的记录链接起来,以允许更复杂的操作,也是有意义的。

如何在 MySQL 中创建外键约束

让我们首先尝试建模 customers 表:

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(250),
last_name VARCHAR(250),
phone_number BIGINT,
);

这个表很简单。它包含用于存储客户名字、姓氏和电话号码的列。它还指定了一个使用 PRIMARY KEY 约束的 ID 列。如果未指定 ID,则使用 serial 别名自动生成序列中的下一个 ID。

对于 orders 表,我们希望能够指定有关单个订单的信息。一个重要的数据是哪个客户下达了订单。我们可以使用外键将订单链接到客户,而无需重复信息。我们通过 FOREIGN KEY 约束来实现这一点,它定义了一个外键关系到另一个表中的列:

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date DATE,
customer BIGINT UNSIGNED,
FOREIGN KEY (customer) REFERENCES customers(customer_id)
);

在这里,我们指出 orders 表中的 customer 列与 customers 表中的 customer_id 列存在外键关系。

我们必须确保外键列的类型与外表使用的类型兼容。customers 表中的 customer_id 列使用 SERIAL 别名,它代表 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,因此我们可以使用 BIGINT UNSIGNED 作为 orders 表中 customer 列的数据类型以匹配。

如果我们尝试向 orders 表中插入一个不引用有效客户的值,MySQL 将拒绝它:

INSERT INTO orders VALUES (
100,
'2019-11-19',
300
);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`prisma`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer`) REFERENCES `customers` (`customer_id`))

如果我们先添加客户,我们的订单就会被系统接受:

INSERT INTO customers VALUES (
300,
'Jill',
'Smith',
5551235677
);
INSERT INTO orders VALUES (
100,
'2019-11-19',
300
);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

虽然主键是外键的绝佳候选,因为它保证只匹配一条记录,但您也可以使用其他列,只要它们是唯一的。

您还可以使用一组保证唯一的列:

CREATE TABLE example (
. . .
FOREIGN KEY (column1, column2) REFERENCES other_table (column1, column2)
);

删除或更新时如何处理外键

定义外键约束时需要考虑的一个问题是,当引用的表值被删除或更新时该如何处理。

例如,我们再次看看 customersorders 表。我们需要指定当客户在 customers 表中有关联订单记录时,我们希望系统如何响应从 customers 表中删除客户记录的行为。

我们可以选择以下选项:

  • RESTRICT:选择限制删除意味着如果 customer 记录被 orders 表中的记录引用,MySQL 将拒绝删除该记录。要删除客户,您必须首先删除 orders 表中所有相关的记录。只有这样,您才能从客户表中删除该值。这是默认操作。
  • CASCADE:选择级联选项意味着当我们删除 customer 记录时,orders 表中引用它的记录也会被删除。这在许多情况下很有用,但必须小心使用,以避免误删数据。
  • NO ACTION:虽然其他一些数据库系统允许您使用 NO ACTION 选项推迟检查,但在 MySQL 中,这等同于 RESTRICT。系统将拒绝更新或删除请求。
  • SET NULL:此选项告诉 MySQL 在引用记录被删除时将引用列设置为 NULL。因此,如果我们从 customers 表中删除客户,orders 表中的 customer 列将被设置为 NULL
  • SET DEFAULT:尽管其他一些数据库系统允许您在引用删除或更新的情况下将列设置为默认值,但 MySQL 实际上不允许此操作,并且不允许您使用此选项定义表。

这些操作可以在定义外键约束时通过添加 ON DELETE 后跟操作来指定。因此,如果我们要删除客户时从系统中删除关联订单,我们可以这样指定:

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date DATE,
customer INT,
FOREIGN KEY (customer) REFERENCES customers(customer_id) ON DELETE CASCADE
);

这些类型的操作也可以在更新引用列而不是删除引用列时应用,只需使用 ON UPDATE 而不是 ON DELETE

结论

在本指南中,我们介绍了约束是什么,以及它们如何帮助您控制输入到 MySQL 表中的数据。我们讨论了列约束和表约束之间的区别,以及使用表格式所提供的更大灵活性。然后,我们回顾了 MySQL 支持哪些约束以及如何在您的表中使用它们。

约束帮助您定义表列的确切要求,因此它们在许多场景中都是不可或缺的。了解各种约束的工作方式以及它们可以帮助您预防哪些问题,将对确保您的数据符合您要求的标准大有帮助。一旦定义,MySQL 可以帮助您自动强制执行约束,从而在问题发生之前进行预防。

关于作者
Justin Ellingwood

Justin Ellingwood

Justin 自 2013 年以来一直撰写有关数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不需要以第三人称写作,这对所有相关方来说都是一种解脱。
© . All rights reserved.