Unique constraint with soft deleted rows excluded

Question

We have a table with a unique constraint on it, for feedback left from one user, for another, in relation to a sale.

ALTER TABLE feedback
ADD CONSTRAINT unique_user_subject_and_sale
UNIQUE (user_id, subject_id, sale_id)

This ensures we don't accidentally get duplicated rows of feedback.

Currently we sometimes hard-delete feedback left in error and left the user leave it again. We want to change to soft-delete:

ALTER TABLE feedback
ADD COLUMN deleted_at timestamptz

If deleted_at IS NOT NULL, consider the feedback deleted, though we still have the audit trail in our DB (and will probably show it ghosted out to site admins).

How can we keep our unique constraint when we're using soft-delete like this? Is it possible without using a more general CHECK() constraint the does an aggregate check (I've never tried using check constraint like this).

It's like I need to append a WHERE clause to the constraint.

Answer 1

Your unique index, later edited out.

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_null
ON feedback(user_id, subject_id, sale_id)
WHERE deleted_at IS NULL

Your unique index has at least two side effects that might cause you some trouble.

1. In other tables, you can't set a foreign key constraint that references "feedback". A foreign key reference requires some combination of columns to be declared as either primary key or unique.
2. Your unique index allows multiple rows that differ only in the "deleted_at" timestamp. So it's possible to end up with rows that look like the example below. Whether this is a problem is application-dependent.

Example

user_id  subject_id  sale_id  deleted_at
--
1        1           1        2012-01-01 08:00:01.33
1        1           1        2012-01-01 08:00:01.34
1        1           1        2012-01-01 08:00:01.35

PostgreSQL documents this kind of index as a partial index, should you need to Google it sometime. Other platforms use different terms for it--filtered index is one. You can limit the problems to a certain extent with a pair of partial indexes.

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_null
ON feedback(user_id, subject_id, sale_id)
WHERE deleted_at IS NULL

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_not_null
ON feedback(user_id, subject_id, sale_id)
WHERE deleted_at IS NOT NULL

But I see no reason to go to this much trouble, especially given the potential problems with foreign keys. If your table looks like this

create table feedback (
  feedback_id integer primary key,
  user_id ...
  subject_id ...
  sale_id ...
  deleted_at ...
  constraint unique_user_subj_sale 
    unique (user_id, subject_id, sale_id)
);

then all you need is that unique constraint on {user_id, subject_id, sale_id}. You might further consider making all deletes use the "deleted_at" column instead of doing a hard delete.

Answer 2

Despite the fact the PostgreSQL documentation advises against using a unique index instead of a constraint (if the point is to have a constraint), it appears you can do

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale
ON feedback(user_id, subject_id, sale_id)
WHERE deleted_at IS NULL