MySQL 列和表约束简介

列约束

列约束 是附加到单个列 的约束。它们用于确定列的建议值是否有效。列约束是在对输入进行基本类型要求验证（例如，确保值对于 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),
    . . .
);

检查约束

检查约束 是一种通用约束，允许您指定涉及列或表值的表达式，该表达式计算为布尔值。

您之前已经看到了一些检查约束的示例。检查约束以关键字 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 拒绝提交。

非空约束

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

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

INSERT INTO national_capitals VALUES (
    NULL,
    'London',
);

ERROR 1048 (23000): Column 'country' cannot be null

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

唯一约束

The 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'

主键约束

The 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 表用于跟踪联系信息和客户信息。将这些信息分开放置是有意义的，因为客户可能有多个订单。但是，能够轻松链接这两个表中的记录也是有意义的，以便执行更复杂的运算。

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

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

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

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