PostgreSQL 唯一索引

本文介绍在 PostgreSQL 中如何创建唯一索引以确保一列或多列中值的唯一性。

PostgreSQL UNIQUE 索引用于强制一列或多列中值的唯一性。

PostgreSQL UNIQUE 索引介绍

只有索引类型为 B-Tree 的索引可以声明为唯一索引。 要创建 UNIQUE 索引，您可以使用以下语法：

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

UNIQUE 关键字用于将索引声明为唯一索引。

如果一个列被定义为唯一索引，那么该列不能存储具有相同的值。

如果两列或更多列被定义为唯一索引，则这些列中的组合值不能重复。

但是，您可以在具有 UNIQUE 索引的列中使用多个 NULL 值 。

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

PostgreSQL UNIQUE 索引示例

以下语句创建一个名为 staff 的表：

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

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

要显示 staff 表的索引，请使用以下语句：

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

 tablename |    indexname    |                                indexdef
-----------+-----------------+-------------------------------------------------------------------------
 staff     | staff_pkey      | CREATE UNIQUE INDEX staff_pkey ON public.staff USING btree (id)
 staff     | staff_email_key | CREATE UNIQUE INDEX staff_email_key ON public.staff USING btree (email)
(2 rows)

PostgreSQL UNIQUE 索引 – 单列示例

以下语句将 mobile_phone 列添加到 staff 表中：

ALTER TABLE staff
ADD mobile_phone VARCHAR(20);

为确保所有员工的手机号码都是不同的，您可以为该 mobile_phone 列定义一个 UNIQUE 索引，如下所示：

CREATE UNIQUE INDEX idx_staff_mobile_phone
ON staff(mobile_phone);

让我们来测试一下。

首先，在 staff 表中插入一个新行：

INSERT INTO staff(first_name, last_name, email, mobile_phone)
VALUES ('Adam','Z','[email protected]', '13333333333');

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

INSERT INTO staff(first_name, last_name, email, mobile_phone)
VALUES ('Jack','W','[email protected]', '13333333333');

由于手机号重复，PostgreSQL 出现如下错误：

ERROR:  duplicate key value violates unique constraint "idx_staff_mobile_phone"
DETAIL:  Key (mobile_phone)=(13333333333) already exists.

PostgreSQL UNIQUE 索引 – 多列示例

以下语句将添加两个新列 work_phone 和 extension 到 staff 表中：

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

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

CREATE UNIQUE INDEX idx_staff_workphone
ON staff(work_phone, extension);

要测试这个索引，首先在 staff 表中插入一行：

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Lily', 'Bush', '1234567','3564');

其次，插入另一个具有相同工作电话号码但分机号不同的员工：

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Joan', 'Doe', '1234567','3565');

该语句有效，因为 work_phone 和 extension 列中的值组合是唯一的。

第三，尝试在 staff 表中的 work_phone 和 extension 列中插入具有相同值的行：

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Tommy', 'Stark', '1234567','3565');

PostgreSQL 发出以下错误：

ERROR:  duplicate key value violates unique constraint "idx_staff_workphone"
DETAIL:  Key (work_phone, extension)=(1234567, 3565) already exists.

结论

本文讨论了 PostgreSQL UNIQUE 索引的用法， UNIQUE 索引用于强制一列或多列中值的唯一性。