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.

Code

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!

Notes

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.