MySQL - 约束

MySQL - 使用约束实现现代数据完整性

理解约束

在数据库设计中，约束是对表中数据列强制执行的规则。它们的主要目的是确保数据的准确性、可靠性和完整性。通过限制可以插入、更新或删除的数据类型，约束可以防止无效数据存储在数据库中。

约束在通过 CREATE TABLE 创建表时定义，或者稍后使用 ALTER TABLE 语句添加。明确命名您的约束是便于管理的一种最佳实践。

列级约束 vs. 表级约束

• 列级约束：应用于单个列。它们作为列定义的一部分进行定义。
• 表级约束：应用于表中的一个或多个列。它们在所有列定义之后单独定义，并且对于多列约束（例如复合主键）是必需的。

MySQL 中常用的约束和属性包括 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT 和 AUTO_INCREMENT。

创建带约束的表的通用语法

CREATE TABLE table_name (
    column_1_name data_type [column_constraint],
    column_2_name data_type [column_constraint],
    ...
    [CONSTRAINT constraint_name] table_constraint (column_or_columns)
);

NOT NULL 约束

默认情况下，列可以包含 NULL 值。NOT NULL 约束确保列不能包含 NULL 值。这对于必须始终包含数据的字段（例如用户的电子邮件或产品 ID）至关重要。

示例

在此示例中，我们创建一个 Products 表，其中 product_id 和 product_name 不能为空。

CREATE TABLE Products (
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    description TEXT
);

尝试插入 product_name 值为 NULL 的记录将失败。

-- 这将成功
INSERT INTO Products(product_id, product_name, price) VALUES(1, 'Wireless Mouse', 29.99);

-- 这将失败
INSERT INTO Products(product_id, product_name, price) VALUES(2, NULL, 129.99);

MySQL 将返回错误，保护数据完整性。

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

UNIQUE 约束

UNIQUE 约束确保列（或一组列）中的所有值互不相同。这对于电子邮件地址或用户名等必须唯一但不是主键的字段非常理想。一个表可以有多个 UNIQUE 约束。

示例

在这里，我们确保每个用户都有一个唯一的 email。

CREATE TABLE Users (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    -- 命名约束是一个好习惯
    CONSTRAINT uq_email UNIQUE (email)
);

让我们尝试插入两个具有相同电子邮件地址的用户。

-- 这将成功
INSERT INTO Users(user_id, username, email) VALUES(101, 'john_doe', 'john.doe@example.com');

-- 这将失败
INSERT INTO Users(user_id, username, email) VALUES(102, 'johnny_d', 'john.doe@example.com');

第二次插入失败，因为电子邮件不是唯一的。

ERROR 1062 (23000): Duplicate entry 'john.doe@example.com' for key 'users.uq_email'

PRIMARY KEY 约束

PRIMARY KEY（主键）约束唯一标识表中的每条记录。主键必须包含 UNIQUE（唯一）值，并且不能包含 NULL 值。它是 NOT NULL 和 UNIQUE 的组合。每个表只能有一个主键，主键可以由单列或多列（复合键）组成。

示例

user_id 列是 Users 表的主键。

CREATE TABLE Users (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    PRIMARY KEY(user_id)
);

任何尝试插入重复或 NULL 的 user_id 都将被拒绝。

-- 这将成功
INSERT INTO Users(user_id, username, email) VALUES(1, 'jane_doe', 'jane.doe@example.com');

-- 这将失败（重复条目）
INSERT INTO Users(user_id, username, email) VALUES(1, 'jane_d', 'jane.d@example.com');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'

-- 这也将失败（NULL 值）
INSERT INTO Users(user_id, username, email) VALUES(NULL, 'john_smith', 'j.smith@example.com');
-- ERROR 1048 (23000): Column 'user_id' cannot be null

FOREIGN KEY 约束

FOREIGN KEY（外键）是用于连接两个表的键。它是一个表（称为子表）中的字段（或字段集合），引用另一个表（父表）中的 PRIMARY KEY。这建立了引用完整性，防止执行会导致孤立记录的操作。

您还可以为 ON DELETE 和 ON UPDATE 事件指定操作，例如 CASCADE（级联）、SET NULL（设置为 NULL）或 RESTRICT（限制）。

示例

首先，我们创建父表 Customers。

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

接下来，我们创建子表 Orders，它链接到 Customers。

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    ON DELETE SET NULL -- 如果客户被删除，将 Orders 表中的 customer_id 设置为 NULL
    ON UPDATE CASCADE -- 如果 customer_id 更新了，Orders 表中也更新
);

CHECK 约束

CHECK 约束用于限制可以放置在列中的值范围。如果您在列上定义了 CHECK 约束，它将只允许该列的特定值。注意：MySQL 从 8.0.16 版本开始强制执行 CHECK 约束。

示例

这个 Products 表确保 price 是一个正数，并且 stock_quantity 不是负数。

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    stock_quantity INT,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

尝试插入价格为 0 的产品将失败。

-- 这将成功
INSERT INTO Products VALUES(1, 'Laptop', 999.99, 50);

-- 这将失败
INSERT INTO Products VALUES(2, 'Sticker', 0.00, 1000);
-- ERROR 3819 (HY000): Check constraint 'chk_price' is violated.

DEFAULT 约束

DEFAULT 约束在 INSERT 操作期间没有为列指定值时，为该列提供一个默认值。

示例

在这个 Orders 表中，如果未提供 status 列的值，它将默认为 ‘Pending’（待处理）。

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending'
);

插入一个新订单，但不指定状态。

INSERT INTO Orders(order_id, order_date) VALUES(1001, '2023-10-27');

-- 现在，让我们查看结果
SELECT * FROM Orders WHERE order_id = 1001;

输出显示 ‘Pending’ 已自动应用。

order_id	order_date	status
1001	2023-10-27	Pending

AUTO_INCREMENT 属性

AUTO_INCREMENT 不是一个约束，而是一个列属性，它允许在向表中插入新记录时自动生成唯一的数字。它最常用于主键列。默认情况下，起始值为 1，并且每条新记录递增 1。

示例

employee_id 将自动生成。

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

我们插入新员工时没有指定 employee_id。

INSERT INTO Employees(first_name, last_name) VALUES('Maria', 'Jones');
INSERT INTO Employees(first_name, last_name) VALUES('David', 'Smith');

让我们看看生成的 ID。

SELECT * FROM Employees;

employee_id	first_name	last_name
1	Maria	Jones
2	David	Smith

管理索引（性能调优）

尽管 INDEX 不是数据完整性约束，但它是数据库 schema 设计的关键部分。索引用于更快地从数据库中检索数据。PRIMARY KEY 和 UNIQUE 约束在幕后自动创建索引。您也可以在 WHERE 子句中经常使用的其他列上手动创建索引，以加快查询速度。

示例

如果您经常按姓氏搜索员工，那么在 last_name 列上创建索引是个好主意。

CREATE INDEX idx_lastname ON Employees(last_name);

此索引对用户不可见，但将由 MySQL 查询优化器用于加速基于 last_name 的查找。