PostgreSQL 教程: UNIQUE INDEX 唯一索引

九月 11, 2023

摘要:在本教程中,您将学习如何创建 PostgreSQL 的UNIQUE索引以确保一列或多列中值的唯一性。

PostgreSQL UNIQUE 索引简介

PostgreSQL 的UNIQUE索引强制一列或多列中值的唯一性。要创建UNIQUE索引,您可以使用以下语法:

CREATE UNIQUE INDEX index_name
ON table_name(column_name, [...]);

请注意,只有 B 树索引可以声明为唯一索引。

当您为列定义UNIQUE索引时,该列不能存储具有相同值的多行。

如果为两列或更多列定义UNIQUE索引,则这些列中的组合值不能在多行中重复。

PostgreSQL 将多个NULL视为不同的值,因此,带有UNIQUE索引的列中可以有多个NULL值。

当你为表定义主键唯一约束时,PostgreSQL 会自动创建相应的UNIQUE索引。

PostgreSQL UNIQUE 索引示例

以下语句创建一个表,名为employees

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

在这个语句中,employee_id主键列,email列有唯一约束,因此,PostgreSQL 创建了两个UNIQUE索引,每一列一个。

要显示employees表的索引,请使用以下语句:

SELECT 
    tablename, 
    indexname, 
    indexdef 
FROM 
    pg_indexes 
WHERE 
    tablename = 'employees';

这是输出:

PostgreSQL UNIQUE Index - on primary key and unique constraint

PostgreSQL UNIQUE 索引 – 单列示例

以下语句将mobile_phone列添加到employees表中:

ALTER TABLE employees
ADD mobile_phone VARCHAR(20);

为了确保所有员工的手机号码都是不同的,您为mobile_phone列定义一个UNIQUE索引,如下所示:

CREATE UNIQUE INDEX idx_employees_mobile_phone
ON employees(mobile_phone);

我们来测试一下。

首先,在employees表中插入一个新行

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('John','Doe','john.doe@rockdata.net', '(408)-555-1234');

其次,尝试插入具有相同电话号码的另一行:

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('Mary','Jane','mary.jane@rockdata.net', '(408)-555-1234');

由于手机号码重复,PostgreSQL 出现以下错误:

ERROR:  duplicate key value violates unique constraint "idx_employees_mobile_phone"
DETAIL:  Key (mobile_phone)=((408)-555-1234) already exists.

PostgreSQL UNIQUE 索引 – 多列示例

以下语句将名为work_phoneextension两个新列添加employees表中:

ALTER TABLE employees
ADD work_phone VARCHAR(20),
ADD extension VARCHAR(5);

多名员工可以共享同一个工作电话号码。但是,它们不能具有相同的分机号码。要强制执行此规则,您可以在work_phoneextension列上定义UNIQUE索引:

CREATE UNIQUE INDEX idx_employees_workphone
ON employees(work_phone, extension);

要测试该索引,首先,向employees表中插入一行:

INSERT INTO employees(first_name, last_name, work_phone, extension)
VALUES('Lily', 'Bush', '(408)-333-1234','1212');

其次,插入另一位具有相同工作电话号码但分机不同的员工:

INSERT INTO employees(first_name, last_name, work_phone, extension)
VALUES('Joan', 'Doe', '(408)-333-1234','1211');

该语句之所以有效,是因为work_phoneextension列中的值组合是唯一的。

第三,尝试在employees表中插入与已存在的work_phoneextension列值具有相同值的行:

INSERT INTO employees(first_name, last_name, work_phone, extension)
VALUES('Tommy', 'Stark', '(408)-333-1234','1211');

PostgreSQL 发出以下错误:

ERROR:  duplicate key value violates unique constraint "idx_employees_workphone"
DETAIL:  Key (work_phone, extension)=((408)-333-1234, 1211) already exists.

在本教程中,您学习了如何使用 PostgreSQL 的UNIQUE索引来强制一列或一组列中值的唯一性。