MySQL

MySQL 列和表约束简介

内容

什么是 MySQL 列和表约束？
约束的定义位置：列约束与表约束
为约束创建名称
MySQL 的可用约束列表
结论

分享到

什么是 MySQL 列和表约束？

约束是用户定义的规则，用于定义列或表的有效值。您可以将它们视为额外的限制，比数据类型更严格地缩小可接受值的范围。

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

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

MySQL 允许您创建与特定列关联的约束或与表总体关联的约束。

几乎所有约束都可以以两种形式使用，而无需修改

约束	列	表
CHECK	是	是
NOT NULL	是	否*
UNIQUE	是	是
PRIMARY KEY	是	是
FOREIGN KEY	否	是

*：NOT NULL 不能用作表约束。但是，您可以使用 IS NOT NULL 作为 CHECK 表约束中的语句来近似获得结果。

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

列约束

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

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

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

此代码段定义了一个 person 表，其中一个列是一个名为 age 的 int。 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 开头，然后提供用括号括起来的表达式。对于列约束，这放在数据类型声明之后。对于表约束，这些可以放在与它们交互的列定义后的任何位置。

例如，我们可以创建一个 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 年内。之后，我们有一个表检查约束，确保影片已收到足够的票数以获得提名，并且片长符合“长片”类别的资格。

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

INSERT INTO film_nominations VALUES (
    'A great film',
    'Talented director',
    '2019-07-16',
    117,
    45
);

Query OK, 1 row affected (0.01 sec)

评估为假的值会产生错误，指示未满足约束

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

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'

如果我们仍然想确保我们不会最终得到重复的条目，同时允许单个列中存在重复值，那么对 country 和 capital 组合的唯一性检查就足够了

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)
);

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

定义外键约束时，您需要考虑的一个问题是，当引用的表值被删除或更新时该怎么办。

例如，让我们再次看一下 customers 和 orders 表。我们需要指定当我们从 customers 表中删除客户时，如果该客户在 orders 表中有关联的订单，我们希望系统如何响应。

我们可以从以下选项中选择

RESTRICT：选择限制删除意味着，如果 orders 表中的记录引用了 customer 记录，则 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 自 2013 年以来一直撰写关于数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。

MySQL 数据类型简介

设置本地 SQLite 数据库

在 GitHub 上编辑此页