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) );
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.