sql-unique-index

SQL 唯一索引与唯一约束

唯一索引（Unique Index）在数据库中主要有两个用途：它通过确保列（或多列组合）中的所有值都是唯一的来强制执行数据完整性，并通过允许数据库基于这些唯一值快速查找行来提高查询性能。它是维护数据整洁和优化查找的强大工具。

唯一索引与唯一约束的区别

在现代 SQL 中，UNIQUE 约束（唯一约束）和 UNIQUE 索引（唯一索引）的概念密切相关。当你在一列上声明 UNIQUE 约束时，数据库系统通常会在幕后创建一个唯一索引来强制执行此约束。最佳实践是使用 UNIQUE 约束语法，因为它更清晰地声明了强制执行业务规则的意图。

UNIQUE 约束：定义在表上的一条规则，用于防止一个或多个列中出现重复值。它是表定义的一部分。
UNIQUE 索引：数据库用于高效强制执行唯一性规则并加速查询的底层数据结构。你也可以直接创建唯一索引。

语法

虽然你可以使用 CREATE UNIQUE INDEX，但更标准和声明式的方法是在表创建时或稍后修改表时使用约束。

-- 方法 1：在表创建时
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2)
);

-- 方法 2：为现有表添加约束
ALTER TABLE products
ADD CONSTRAINT uq_product_code UNIQUE (product_code);

示例：强制唯一性

让我们使用一个 products 表。product_code 必须对每个产品都是唯一的。

INSERT INTO products (product_code, name, price) VALUES ('A-101', 'Super Widget', 19.99);
-- 这次插入成功。

INSERT INTO products (product_code, name, price) VALUES ('A-102', 'Mega Gadget', 29.99);
-- 这次插入也成功。

现在，如果我们尝试插入另一个具有现有 product_code 的产品，数据库将抛出错误，从而保护我们的数据完整性。

INSERT INTO products (product_code, name, price) VALUES ('A-101', 'Another Widget', 15.99);

-- 输出将是一个错误：
-- ERROR: duplicate key value violates unique constraint "uq_product_code"
-- DETAIL:  Key (product_code)=(A-101) already exists.

NULL 值的作用

唯一索引/约束如何处理 NULL 值可能因数据库系统而异，这是跨平台开发的关键点。

SQL 标准 (PostgreSQL, Oracle 等)：标准行为是允许唯一列中存在多个 NULL 值。这是因为在 SQL 中，NULL 不被认为等于任何其他值，包括另一个 NULL。
非标准 (SQL Server)：SQL Server 强制执行更严格的规则，只允许唯一约束列中存在一个 NULL 值。

在唯一约束中关于 NULL 值的处理，请务必查阅你所使用的具体数据库的文档。

创建复合唯一索引

有时，唯一性是由多列组合定义的。例如，在一个 course_enrollments（课程注册）表中，一个学生可以注册多门课程，一门课程可以有多个学生，但一个学生只能注册同一门课程一次。因此，student_id 和 course_id 的组合必须是唯一的。

示例

CREATE TABLE course_enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    -- 复合唯一约束
    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

此约束允许以下插入操作：

INSERT INTO course_enrollments (student_id, course_id) VALUES (1, 101); -- 成功
INSERT INTO course_enrollments (student_id, course_id) VALUES (1, 102); -- 成功
INSERT INTO course_enrollments (student_id, course_id) VALUES (2, 101); -- 成功

但它将阻止重复注册：

INSERT INTO course_enrollments (student_id, course_id) VALUES (1, 101);
-- ERROR: duplicate key value violates unique constraint "uq_student_course"

性能权衡

虽然索引对于性能至关重要，但它们并非没有代价。理解其中的权衡是良好数据库设计的关键。

读取性能（优势）：索引显著加快了在 WHERE 子句中使用索引列的 SELECT、UPDATE 和 DELETE 查询。
写入性能（成本）：INSERT、UPDATE 和 DELETE 操作会略微变慢，因为除了修改表数据外，数据库还必须更新索引。对于写入量非常大的表，应谨慎选择索引数量。

验证索引

你可以查询数据库的元数据来查看表上存在的索引。虽然有些数据库有简单的命令（如 MySQL 中的 SHOW INDEX FROM table），但更具可移植性的方法是查询 INFORMATION_SCHEMA。

标准 SQL 方法

SELECT
    indexname,
    indexdef
FROM
    pg_indexes -- 这是 PostgreSQL 特有的，但其他数据库也有类似的视图
WHERE
    tablename = 'products';

对于 MySQL，你可以使用：

SHOW INDEX FROM products;

输出将列出表上的索引，包括主键索引和你创建的唯一索引/约束。