九月 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 索引 – 单列示例
以下语句将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_phone
和extension
的两个新列添加到employees
表中:
ALTER TABLE employees
ADD work_phone VARCHAR(20),
ADD extension VARCHAR(5);
多名员工可以共享同一个工作电话号码。但是,它们不能具有相同的分机号码。要强制执行此规则,您可以在work_phone
和extension
列上定义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_phone
和extension
列中的值组合是唯一的。
第三,尝试在employees
表中插入与已存在的work_phone
和extension
列值具有相同值的行:
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
索引来强制一列或一组列中值的唯一性。