postgre如何在SQL数据库中基于特定条件强制唯一性约束

Question

我正在研究一个SQL数据库，我有以下表:

锻炼例程

id serial PRIMARY KEY,
user_id integer
REFERENCES users (id)
ON DELETE CASCADE
NOT NULL,
name varchar(255) NOT NULL,
active boolean DEFAULT TRUE,
UNIQUE(user_id, name)

目前，user_id和name的组合应该是唯一的，这意味着用户不能有两个具有相同名称的锻炼例程。然而，这并不是我想要的。

相反，我希望user_id和name的组合是唯一的仅在active = true。换句话说，用户应该能够有多个同名的非活动锻炼，但不应该允许有重复的活动锻炼。

是否有办法在这个表中强制执行?

Answer 1

部分索引可用于:

CREATE UNIQUE INDEX ON table_name (user_id, name) WHERE active;

小提琴

Answer 2

您可以使用部分索引来实现这一点。索引将仅用于包含活动列的查询，并且仅用于包含值为true的活动列的查询。这意味着不包含活动列的查询将不会使用索引，而包含活动列且值为false的查询将不会使用索引。

CREATE UNIQUE INDEX workout_routine_user_id_name_active
ON workout_routine (user_id, name)
WHERE active = true;

Answer 3

我是一个Oracle家伙-但也许我可以在这里提供帮助。是的，你可以用几种方法建模你想要的。

一种方法是创建两个表，一个是历史表，另一个是当前表。历史数据除了代理键ID上的PK之外没有唯一索引，而当前数据在user_id和name上也有唯一索引。

第二种方法，使用单个表，是添加一个可空日期字段，表示已关闭/未活动的日期。NULL表示活动，非NULL(日期值)表示非活动。在user_id,name,inactive_date上创建一个唯一的索引(不是PK)。如果SQL Server像Oracle一样，在唯一约束中允许NULL值，但不允许多个NULL值，这将强制执行当前user_id的名称只能有一个实例(具有NULL inactive_date)，但允许有许多非活动行，因为它们都具有不同的日期值。

如果SQL Server的行为与Oracle不同，请检查"NULLS NOT DISTINCT"选择。