PostgreSQL 唯一约束与排除约束

在本文中，我们将介绍 PostgreSQL 数据库中的唯一约束和排除约束的使用方法。唯一约束用于确保表中的某个列或列的组合的值是唯一的，而排除约束用于限制表中的某些值的组合。

唯一约束

在 PostgreSQL 中，唯一约束用于确保表中的某些列的值是唯一的。唯一约束可以应用于单个列，也可以应用于多个列的组合。

创建唯一约束

我们可以在创建表的过程中定义唯一约束，也可以在表创建之后通过 ALTER TABLE 命令来添加唯一约束。

以下是在创建表时定义唯一约束的语法：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2, ...)
);

以下是在表创建之后添加唯一约束的语法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

示例

假设我们有一个名为 students 的表，其结构如下：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

我们希望确保 email 列中的值是唯一的，可以通过以下方法添加唯一约束：

ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE (email);

违反唯一约束

如果尝试插入或更新表中某个列的值，而该值在唯一约束下已经存在，则会触发唯一约束违反错误。此时，数据库会拒绝这个操作并返回错误信息。

以下是一个示例：

INSERT INTO students (name, email)
VALUES ('Alice', 'alice@example.com');

-- 尝试再次插入相同的 email
INSERT INTO students (name, email)
VALUES ('Bob', 'alice@example.com');

上述示例中，第一次插入操作将成功执行，而第二次插入操作将触发唯一约束违反错误，因为 alice@example.com 已经存在于 email 列中。

排除约束

在 PostgreSQL 中，排除约束用于限制表中某些值的组合。通过排除约束，我们可以定义规则来防止插入或更新特定值的组合。

创建排除约束

我们可以在创建表的过程中定义排除约束，也可以在表创建之后通过 ALTER TABLE 命令来添加排除约束。

以下是在创建表时定义排除约束的语法：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name EXCLUDE (column1 WITH operator, column2 WITH operator, ...)
    WHERE (condition)
);

以下是在表创建之后添加排除约束的语法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name EXCLUDE (column1 WITH operator, column2 WITH operator, ...)
WHERE (condition);

示例

假设我们有一个名为 appointments 的表，其结构如下：

CREATE TABLE appointments (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

我们希望确保在 appointments 表中没有时间交叉的记录。可以通过以下方法添加排除约束：

ALTER TABLE appointments
ADD CONSTRAINT exclude_time_overlap
EXCLUDE USING gist (tsrange(start_time, end_time) WITH &&);

违反排除约束

如果尝试插入或更新表中某些值的组合，而这些值违反了排除约束，则会触发排除约束违反错误。此时，数据库会拒绝这个操作并返回错误信息。

以下是一个示例：

-- 插入一个时间区间为 '2022-01-01 10:00' 到 '2022-01-01 12:00' 的记录
INSERT INTO appointments (start_time, end_time)
VALUES ('2022-01-01 10:00', '2022-01-01 12:00');

-- 尝试插入一个时间区间为 '2022-01-01 11:00' 到 '2022-01-01 13:00' 的记录
INSERT INTO appointments (start_time, end_time)
VALUES ('2022-01-01 11:00', '2022-01-01 13:00');

上述示例中，第一次插入操作将成功执行，而第二次插入操作将触发排除约束违反错误，因为时间区间存在交叉。

总结

在本文中，我们介绍了 PostgreSQL 数据库中的唯一约束和排除约束的使用方法。唯一约束用于确保表中某个列或列的组合的值是唯一的，而排除约束用于限制表中某些值的组合。我们了解了如何创建、添加和违反这两种约束，并通过示例进行了说明。使用唯一约束和排除约束可以提高数据的完整性和准确性，避免重复或冲突的数据存在。