Fun With SQL Constraints

Say you are managing entities in SQL and each entity has multiple subentities, of which exactly one can be "core":

create table entities(
    _entity_id serial primary key,
    entity_name text,
    unique(entity_name)
);

create table subentities(
    _subentity_id serial primary key,
    _entity_id int not null references entities(_entity_id),
    subentity_name text not null,
    is_core boolean not null,
    unique(_entity_id, subentity_name)
);

Up until yesterday, I would have enforced the one-core-subentity-per-entity in the client code. And while it is fashionable to play fast and loose with data integrity in the name of performance these days, doing so would have bothered me. Relying on anything other than your database engine to enforce data correctness is ultimately a lot like smoking at a gas station: you'll get away with it for a while, but a stray spark will eventually result in the rapid, unplanned disassembly of your data.

As it turns out, Postgres provides us with a solution in the form of partial indices.

Here is what I ended up doing:

create unique index u_one_active_subent_per_ent on subentities(_entity_id)
    where is_core=TRUE;

This creates an index (duh) that filters subentities for all entities where is_core is set to TRUE. And since it's a unique index, there is an implicit constraint that there can only be one such entry per _entity_id. Here is the index in action:

--1. prime the entity table
insert into auth.entities(entity_name) values ('LEVELCOMPUTE');
--2. insert a subentity where is_core=True
insert into auth.subentities(_entity_id, subentity_name, is_core) values (1, 'LEVELCOMPUTE-1', True);
--3. try doing it again
insert into auth.subentities(_entity_id, subentity_name, is_core) values (1, 'LEVELCOMPUTE-2', True);
--4. resume regular service
insert into auth.subentities(_entity_id, subentity_name, is_core) values( 1, 'LEVELCOMPUTE-2', False);

While (4) succeeds, (3) results in an error as it introduces a second is_core=True for _entity_id=1:

ERROR:  duplicate key value violates unique constraint "u_one_active_se_per_e"
DETAIL:  Key (_entity_id)=(1) already exists.

Nice.

My only wish -- a minor one, natch -- is that all kinds of constraints (even the unusual ones like this) were exposed by the CONSTRAINT keyword.