postgresql - unique with extract constraint

1
votes

Here is the problem I have with unique constraints: let's say I have a table app_user. And it has a column "name", and a column "is_active". So if "is_active" is false, the user is deleted.

Now I want a unique constraint on the column "name". Then a user gets deleted, then someone wants to add again a user with the same name, it fails, because of the unique constraint. I could put that check in the upper layers (and for user-triggered actions which don't really happen that often it's probably safe enough, although it's extra code).

But I'd like to set up a PostgreSQL unique constraint that would operate only on rows where is_active=true.

I've looked at the PostgreSQL 9 EXCLUDES feature.

I came up with something like that:

alter table test add exclude (name with =, is_active with ||)

So the idea is, so that there is a unique conflict, I must have both names equal, and both is_active must be true, too. Sadly the || operator doesn't exist in that case.

Is there anyway that could be achieved? Or do you suggest other solutions that would still respect the requirements (ability to recreate a user with the same name as a deleted user)? One option would be to say, it's not possible, but preventing users from accessing a certain username forever is a bit strong.

Also, business logic doesn't really depend on the non-duplication, but it's still a healthy practice to prevent it. In this case, I'll be importing data in the system, and the import is done by name. So if I have two records with the same name I'll have a problem. If there was a record by that name that was deleted 6 months ago and a new one was created in the meantime, that's not a problem.

And I would be very happy if there was a solution also compatible with other SQL servers, but I doubt that exists... Also, I'd rather not add extra tables to achieve this. So I guess it's either the EXCLUDE way or nothing...

1
postgresqlunique-constraint

1 Answers

3
votes

You don't need an exclusion constraint.

A partial unique index is enough

create unique index unique_active_name 
   on app_user (name)
   where is_active;

(this assumes that is_active is defined as boolean)