CODEWITHSUNDEEP
postgresqldatabase-designconstraints
d11wtq
d11wtq

Reputation: 35318

Unique constraint with soft deleted rows excluded

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.

Upvotes: 20

Views: 17180

Answers (3)

LordF
LordF

Reputation: 496

You could create a constraint with deleted_at signature.

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

This does not create problems like a unique index.

The only problem would be removing user feedback, creating a new one, and then deleting it again in the same time.

And considering the accuracy of the column, the user would have to fit in 1 microsecond. Which, let's face it, is impossible, even if he did it through, the time between these requests will definitely be greater than 1 microsecond.

The only problem is that null value isn't checking in unique. So it won't works.

But you could add a default value for not deleted rows as the lowest value you could store in that column.

If you cannot accept the default value for deleted_at, if the object is not deleted, you can consider adding a deleted_token column and generating a key for it if you delete a value, and for an undeleted object, keep 0 or whatever.

Upvotes: -1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95662

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.

Upvotes: 36

d11wtq
d11wtq

Reputation: 35318

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

Upvotes: 8

Related Questions