Self-referencing 1-1 rel. integrity in PostgreSQL
Enforcing consitency for self-referncing tables with 1-to-1 relationships in PostgreSQL. Examples and code.
One-to-one relationship consistency
Ensuring consistency of one-to-one relationships in sql is usually really easy: A normalised table usually doesn't have one-to-one relationships (they would end up being in the same table). Therfore - usually - the problem doesn't arrise in first place.
However, sometimes 1-to-1 relationships are needed: This could either be due tode-normalisation or because the entry is on the same table. In particular the second case is common ("one person is married to exactly another person") and can't easily be avoided.
Trivial example 1: Different tables
Each car has a registration - each registration has exactly one car.
In order to avoid inconsistencies, this relationship would only be saved on either the registration or the car, but never both!
Trivial example 2: Same table
Each person has another person that is their best friend.
Peter's best friend is Mary. Mary's best friend is Paul. Paul's best friend is Jerry.
In this setup, each entry has a foreign key that refers to the primary id of it's own table!
Not so trivial example: Same table, both ways
Each person has a spouse. If Peter is Mary's spouse, then Mary is Peter's spouse.
This is tricky, because there must be a foreign key in both, Peter and Mary, (as they are entries from the same table). That means, technically, they could not refer to each other. However, that's not what we want. We therefore must ensure consitency on insert/update: It is required to update the other side of the relationship to be consistent, that is, point to the just inserted/updated entry. On updates and deletes, we additionally need to ensure that the entry that the update has been pointing to originally is unset. This can easily be done using PostgreSQL functions adn triggers.
create or replace function sync_spouse() returns trigger language plpgsql as $$ begin IF TG_OP = 'INSERT' THEN UPDATE person SET spouse = new.id WHERE id = new.spouse; END IF; IF TG_OP = 'UPDATE' AND old.spouse != new.spouse THEN UPDATE person SET spouse = NULL WHERE id = old.spouse; UPDATE person SET spouse = new.id WHERE id = new.spouse; END IF; IF TG_OP = 'DELETE' THEN UPDATE person SET spouse = NULL WHERE id = old.spouse; END IF; RETURN NEW; end $$; create trigger sync_spouse after insert or update on Person for each row execute procedure sync_spouse();
The above code runs on each insert, update and delete and ensures the consistency of the database entries.
With that, a person always references exactly one other person and vice-versa, and consitency of the data is guaranteed!
For the delete case, instead of using the function above, one may rely using
ON DELETE SET NULL.
An alternative to the above approach is using Rules on Insert, Update and Delete, to the same effect.