Let’s say you’re operating a zoo, and you have this simple constraint:
You can put many animals of the same type into a single cage; or distribute them among many cages; but you cannot mix animals of different types within a single cage.
This rule prevents, for example, assigning a zebra to live in the same cage as a lion. Simple, right?
How do you enforce it? Any ideas yet? Keep reading: I will present a solution that uses a generalization of the standard UNIQUE constraint.
(Don’t dismiss the problem too quickly. As with most simple-sounding problems, it’s a fairly general problem with many applications.)
First of all, let me say that, in one sense, it’s easy to solve: see if there are any animals already assigned to the cage, and if so, make sure they are the same type. That has two problems:
- You have to remember to do that each time. It’s extra code to maintain, possibly an extra round-trip, slightly annoying, and won’t work unless all access to the database goes through that code path.
- More subtly, the pattern read, decide what to write, write is prone to race conditions when another process writes after you read and before you write. Without excessive locking, solving this is hard to get right — and likely to pass tests during development before failing in production.
[ Aside: if you use true serializability in PostgreSQL 9.1, that completely solves problem #2, but problem #1 remains. ]
Those are exactly the kinds of problems that a DBMS is meant to solve. But what to do? Unique indexes don’t seem to solve the problem very directly, and neither do foreign keys. I believe that they can be combined to solve the problem by using two unique indexes, a foreign key, and an extra table, but that sounds painful (perhaps someone else has a simpler way to accomplish this with SQL standard features?). Row locking and triggers might be an alternative, but also not a very clean solution.
A better solution exists in PostgreSQL 9.1 using Exclusion Constraints (Exclusion Constraints were introduced in 9.0, but this solution requires the slightly-more-powerful version in 9.1). If you have never seen an Exclusion Constraint before, I suggest reading a previous post of mine.
Exclusion Constraints have the following semantics (copied from documentation link above):
The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE. If all of the specified operators test for equality, this is equivalent to a UNIQUE constraint…
First, as a prerequisite, we need to install
btree_gist into our database (make sure you have the contrib package itself installed first):
CREATE EXTENSION btree_gist;
Now, we can use an exclude constraint like so:
CREATE TABLE zoo ( animal_name TEXT, animal_type TEXT, cage INTEGER, UNIQUE (animal_name), EXCLUDE USING gist (animal_type WITH <>, cage WITH =) );
Working from the definition above, what does this exclusion constraint mean? If any two tuples in the relation are ever compared (let’s call these TupleA and TupleB), then the following will never evaluate to TRUE:
TupleA.animal_type <> TupleB.animal_type AND TupleA.cage = TupleB.cage
[ Observe how this would be equivalent to a UNIQUE constraint if both operators were "=". The trick is that we can use a different operator -- in this case, "<>" (not equals). ]
=> insert into zoo values('Zap', 'zebra', 1); INSERT 0 1 => insert into zoo values('Larry', 'lion', 2); INSERT 0 1 => insert into zoo values('Zachary', 'zebra', 1); INSERT 0 1 => insert into zoo values('Zeta', 'zebra', 2); ERROR: conflicting key value violates exclusion constraint "zoo_animal_type_cage_excl" DETAIL: Key (animal_type, cage)=(zebra, 2) conflicts with existing key (animal_type, cage)=(lion, 2). => insert into zoo values('Zeta', 'zebra', 3); INSERT 0 1 => insert into zoo values('Lenny', 'lion', 2); INSERT 0 1 => insert into zoo values('Lance', 'lion', 1); ERROR: conflicting key value violates exclusion constraint "zoo_animal_type_cage_excl" DETAIL: Key (animal_type, cage)=(lion, 1) conflicts with existing key (animal_type, cage)=(zebra, 1). => select * from zoo order by cage;
animal_name | animal_type | cage -------------+-------------+------ Zap | zebra | 1 Zachary | zebra | 1 Larry | lion | 2 Lenny | lion | 2 Zeta | zebra | 3 (5 rows)
And that is precisely the constraint that we need to enforce!
- The constraint is declarative so you don’t have to deal with different access paths to the database or different versions of the code. Merely the fact that the constraint exists means that PostgreSQL will guarantee it.
- The constraint is also immune from race conditions — as are all EXCLUDE constraints — because again, PostgreSQL guarantees it.
Those are nice properties to have, and if used properly, will simplify the overall application complexity and improve robustness.