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
ERROR: duplicate key value violates unique constraint "u_one_active_se_per_e" DETAIL: Key (_entity_id)=(1) already exists.
My only wish -- a minor one, natch -- is that all kinds of constraints (even the unusual ones like this) were exposed by the