PostgreSQL 教程: 唯一约束

九月 13, 2023

摘要：在本教程中，您将了解 PostgreSQL 的UNIQUE约束，以确保存储在一个列或一组列中的值在表中的行中是唯一的。

有时，您希望确保存储在列或一组列中的值在整个表中是唯一的，例如电子邮件地址或用户名。

PostgreSQL 为您提供了正确维护数据唯一性的UNIQUE约束。

当UNIQUE约束存在时，每次插入新行时，它都会检查该值是否已在表中。如果该值已存在，它会拒绝更改并发出错误。更新现有数据也是相同的处理过程。

当你向一列或一组列添加UNIQUE约束时，PostgreSQL 将自动在该列或一组列上创建唯一索引。

PostgreSQL UNIQUE 约束示例

以下语句创建一个新表，名为person，在email列上有一个UNIQUE约束。

CREATE TABLE person (
	id SERIAL PRIMARY KEY,
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	email VARCHAR (50) UNIQUE
);

请注意，上面的UNIQUE约束可以重写为表约束，如以下查询所示：

CREATE TABLE person (
	id SERIAL  PRIMARY KEY,
	first_name VARCHAR (50),
	last_name  VARCHAR (50),
	email      VARCHAR (50),
        UNIQUE(email)
);

首先，使用INSERT语句将新行插入person表中：

INSERT INTO person(first_name,last_name,email)
VALUES('john','doe','j.doe@rockdata.net');

其次，插入包含重复电子邮件的另一行。

INSERT INTO person(first_name,last_name,email)
VALUES('jack','doe','j.doe@rockdata.net');

PostgreSQL 发出错误消息。

ERROR:  duplicate key value violates unique constraint "person_email_key"
DETAIL:  Key (email)=(j.doe@rockdata.net) already exists.

在多列上创建 UNIQUE 约束

PostgreSQL 允许您使用以下语法创建对一组列的UNIQUE约束：

CREATE TABLE table (
    c1 data_type,
    c2 data_type,
    c3 data_type,
    UNIQUE (c2, c3)
);

c2 和 c3 列中的值组合在整个表中是唯一的。c2 或 c3 列的值在本列中不必是唯一的。

使用唯一索引添加唯一约束

有时，您可能希望向现有列或列组添加唯一约束。让我们看一下下面的例子。

首先，假设您有一个名为equipment的表：

CREATE TABLE equipment (
	id SERIAL PRIMARY KEY,
	name VARCHAR (50) NOT NULL,
	equip_id VARCHAR (16) NOT NULL
);

其次，根据equip_id列创建唯一索引。

CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_id 
ON equipment (equip_id);

第三，使用equipment_equip_id索引向equipment表添加唯一约束。

ALTER TABLE equipment 
ADD CONSTRAINT unique_equip_id 
UNIQUE USING INDEX equipment_equip_id;

请注意，ALTER TABLE语句获取表上的独占锁。如果您有任何待处理的事务，它将等待所有事务完成后再更改表。因此，您应该使用以下查询检查pg_stat_activity视图，以查看当前正在进行的待处理事务：

SELECT
	datid,
	datname,
    usename,
	state
FROM
	pg_stat_activity;

您应该查看结果以找到具有idle in transaction值的state列。这些是等待完成的事务。

在本教程中，您了解了UNIQUE约束，以及如何使用它们来强制存储在同一表中的一列或一组列中的值在各行中唯一。

搜索

分类

标签