How to enforce a uniqueness constraint in a SQL database based on a certain condition-CodePudding

I'm working on a SQL database, and I have the following table:

Workout Routine

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)

Currently, the combination of user_id, and name are supposed to be unique, meaning that a user cannot have two workout routines with the same name. However, this is not quite what I want.

Instead, I would want the combination user_id and name to be unique only in cases where active = true. In other words, a user should be able to have multiple workouts with the same name that are inactive, but should not be allowed to have duplicates that are active.

Is there a way to enforce this in this table?

CodePudding user response：

A partial index can be used for this:

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

The fiddle

CodePudding user response：

You can use a partial index to achieve this. The index will only be used for queries that include the active column, and will only be used for queries that include the active column with a value of true. This means that queries that do not include the active column will not use the index, and queries that include the active column with a value of false will not use the index.

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

CodePudding user response：

I'm an Oracle guy - but perhaps I can be of help here. Yes, you can model what you want in several ways.

One way is to create two tables, one historical, the other current. The historical would have no unique index other than the PK on the surrogate key ID, whereas the current would also have a unique index on user_id and name.

The second way, using a single table, is to add a nullable date field that represents the closed/inactive date. NULL means active, non-NULL (a date value) would mean inactive. Create a unique index (not a PK) on user_id,name,inactive_date. If SQL Server is like Oracle and allows NULL values in a unique constraint but not multiple NULL values, that will enforce that there can be only one instance of a name for a user_id that is current (having a NULL inactive_date), but allows there to be many inactive rows since they would all have different date values.

If SQL Server acts differently than Oracle then check out the "NULLS NOT DISTINCT" option.