PostgreSQL – Multi-Column Uniqueness Constraint with Conditional Values

constraintpostgresqlunique-constraint

I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example

create table builders (
    id text serial
); 
create table houses (
    id text serial, 
    status text, 
    builder_id text references builders(id)
);

The house status can be "NOT_STARTED", "PLANNING", "CONSTRUCTING", "BUILT" or integers mapping to these values or whatever you want.

There's a rule in my system where a builder can only plan or construct one house at a time, that is, if there are two house rows with builder Id 1 and one has status "PLANNING" and the other has status "CONSTRUCTING", the system is in an inconsistent state. I'd like to enforce this in the database.

I tried doing 

CREATE UNIQUE INDEX CONCURRENTLY builders_unique_house 
ON houses ("status", "builder_id")
WHERE "status" IN ('PLANNING', 'CONSTRUCTING');

I was able to add this index, and it can detect two houses in PLANNING or two in CONSTRUCTING, but it allows one to be in PLANNING and one to be in CONSTRUCTING.

Is there an index or constraint I can add to express this rule? Another possibility I had was adding a boolean to represent "planning or constructing", like in_progress or something and then adding a unique key on that.

Best Answer

You need to remove the status column from your index:

CREATE UNIQUE INDEX builders_unique_house 
   ON houses (builder_id)
WHERE status IN ('PLANNING', 'CONSTRUCTING');

If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique.

If you remove the status, only the builder_id will be indexed, but only if one of the status values are set. And then trying to index "the other" status will lead to the desired unique index violation.

Related Question