sql-constraints

SQL 约束

什么是 SQL 约束？

SQL 约束是对数据列或表强制执行的规则，以确保数据准确性、可靠性和完整性。它们可以阻止无效数据进入数据库。通过定义约束，您可以在数据库级别强制执行应用程序的业务规则，这是健壮系统设计的关键方面。

约束可以在列级别（作为列定义的一部分）或表级别（独立于列定义）定义。当约束涉及多个列时（例如复合主键），表级约束是必需的。

创建约束

约束通常在使用 CREATE TABLE 语句创建表时定义。它们也可以使用 ALTER TABLE 语句添加到现有表中。

-- 创建带有约束的表的一般语法
CREATE TABLE table_name (
    column1 datatype column_level_constraint,
    column2 datatype,
    ...,
    table_level_constraint(column2, ...)
);

NOT NULL 约束

NOT NULL 约束确保列不能包含 NULL 值。这用于必须始终包含值的字段，例如用户的姓名或产品的价格。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL
);

-- 这将失败，因为 `last_name` 为 NOT NULL
INSERT INTO employees (employee_id, first_name, hire_date) VALUES (1, 'John', '2023-01-15');

UNIQUE 约束

UNIQUE 约束确保列中（或一组列中）的所有值彼此不同。与 PRIMARY KEY 不同，一个表可以有多个 UNIQUE 约束，并且在大多数数据库系统中，UNIQUE 列可以接受一个 NULL 值。

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_username UNIQUE (username),
    CONSTRAINT uq_email UNIQUE (email)
);

-- 这将成功
INSERT INTO users VALUES (1, 'jdoe', 'john.doe@example.com');

-- 这将失败，因为电子邮件 'john.doe@example.com' 已经在使用中。
INSERT INTO users VALUES (2, 'asmith', 'john.doe@example.com');

PRIMARY KEY 约束

PRIMARY KEY（主键）是一种特殊约束，它唯一标识表中的每条记录。它实际上是 NOT NULL 和 UNIQUE 的组合。一个表只能有一个 PRIMARY KEY，它可以由单个列或多个列（复合键）组成。

CREATE TABLE products (
    product_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    -- 表级主键定义
    PRIMARY KEY(product_id)
);

FOREIGN KEY 约束（参照完整性）

FOREIGN KEY（外键）在两个表之间创建链接，强制执行参照完整性。它确保一个表中的列（或一组列）的值必须与另一个表的主键中的值匹配。这可以防止出现“孤立”记录，例如不属于任何客户的订单。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date TIMESTAMP NOT NULL,
    customer_id INT NOT NULL,
    -- 将 orders 关联到 customers 的外键
    CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT -- 阻止删除有订单的客户
);

-- 如果 ID 为 999 的客户不存在，这将失败。
INSERT INTO orders VALUES (101, NOW(), 999);

ON DELETE 和 ON UPDATE 子句定义了当父记录（在 customers 中）被删除或更新时，子记录（在 orders 中）会发生什么。常见选项包括 CASCADE（级联）、SET NULL（设置为 NULL）、RESTRICT（限制）和 NO ACTION（不执行任何操作）。

CHECK 约束

CHECK 约束用于验证输入到列中的值。它确保值满足特定条件。像 PostgreSQL、SQL Server 和 MySQL（8.0.16+ 版本）等现代关系型数据库管理系统（RDBMS）完全支持 CHECK 约束。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL,
    CONSTRAINT chk_stock_positive CHECK (stock_quantity >= 0)
);

-- 这将失败，因为价格不大于 0。
INSERT INTO products VALUES (1, 'Sample Product', -50.00, 100);

DEFAULT 约束

DEFAULT 约束为在 INSERT 操作期间未指定值的列提供默认值。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

-- 插入一个使用 order_date 和 status 默认值的订单
INSERT INTO orders (order_id) VALUES (101);
-- 新行将具有当前时间戳和“Pending”状态。

INDEX 伪约束

虽然 INDEX（索引）不是限制数据意义上的约束，但它是一个特殊的查找表，数据库搜索引擎可以用来加快数据检索。在列上创建索引可以显著提高带有 WHERE 子句的 SELECT 查询的性能。但是，它会减慢数据修改（INSERT、UPDATE、DELETE）的速度，因为索引也必须更新。

-- 在 employees 表的 last_name 列上创建索引
CREATE INDEX idx_employees_lastname ON employees(last_name);

-- 这个查询在大型表上现在会快得多。
SELECT * FROM employees WHERE last_name = 'Smith';

修改和删除约束

您可以使用 ALTER TABLE 命令删除现有约束。您通常需要知道约束的名称，这就是为什么明确命名约束（例如 CONSTRAINT fk_orders_customers ...）是最佳实践。

-- 从 ORDERS 表中删除外键约束
ALTER TABLE orders DROP CONSTRAINT fk_orders_customers;

-- 删除主键约束（语法可能因 RDBMS 而异）
ALTER TABLE products DROP PRIMARY KEY;

数据完整性最佳实践

在数据库层强制执行数据完整性是构建可靠应用程序的基础。这个概念与保证可靠事务处理的 ACID 特性（原子性、一致性、隔离性、持久性）密切相关。