I have been thinking about ways to increase data integrity in a relational database. I have found some interesting ways to use foreign-key constraints to that end. Here are some examples. The examples are written for Postgres.

State capital in state

Let's try to model states, cities, and state capitals. Every city belongs to a single state, and every state has exactly one capital. The capital city of a state must be inside that state.

    CREATE TABLE state (
      code varchar(2) PRIMARY KEY,
      name text NOT NULL,
      capital int
    );
    CREATE TABLE city (
      id int PRIMARY KEY,
      name text NOT NULL,
      state varchar(2) REFERENCES state(code) DEFERRABLE INITIALLY DEFERRED
    );
    ALTER TABLE state ADD CONSTRAINT capital_references_city FOREIGN KEY (capital) REFERENCES city(id);

But, now we have a situation where a city can be a capital of a state that it's not in:

    INSERT INTO city (id, name, state) VALUES (1, 'Santa Fe', 'NM');
    INSERT INTO state (code, name, capital) VALUES ('NM', 'New Mexico', 1);

    INSERT INTO city (id, name, state) VALUES (2, 'Albuquerque', 'NM');
    INSERT INTO state (code, name, capital) VALUES ('CO', 'Colorado', 1);

We've set Albuquerque as the capital of Colorado, which should not be allowed according to the specification. In order to prevent this, we can change our foreign-key constraint:

    ALTER TABLE STATE DROP CONSTRAINT capital_references_city;
    -- not semantically necessary because id is already unique, but Postgres requires it
    ALTER TABLE city ADD CONSTRAINT id_state_unique_for_fk_target UNIQUE (id, state);
    ALTER TABLE state ADD CONSTRAINT capital_references_city_in_state FOREIGN KEY (capital, code) REFERENCES city(id, state);

Now we are prevented from inserting the erroneous row:

    INSERT INTO state (code, name, capital) VALUES ('CO', 'Colorado', 1);

    ERROR:  insert or update on table "state" violates foreign key constraint "capital_references_city_in_state"
    DETAIL:  Key (capital, code)=(1, CO) is not present in table "city".

We must insert a city that actually is in Colorado to use it as the capital:

1 2	INSERT INTO city (id, name, state) VALUES (3, 'Denver', 'CO'); INSERT INTO state (code, name, capital) VALUES ('CO', 'Colorado', 3);

And the insert succeeds because the constraint is satisfied.

Retailer's chain relationship must point to a chain

I had a situation where I needed to model retailers and chains. Each retailer can optionally belong to a single chain. In most other respects a chain behaves similarly to a retailer. Importantly, they both have a retailer ID allocated from a single namespace (IDs must be unique across both retailers and chains). If we modeled this as two separate tables, we couldn't prevent a retailer and a chain both having the same ID. If we model it in a single table, we need to ensure that only chains can be marked as retailer's chain. In order to enforce this, we have both a retailer_id and a chain_id column. Chains are denoted by having their retailer_id match their chain_id.

    CREATE TABLE retailer (
      retailer_id int PRIMARY KEY,
      chain_id int,
      name varchar(100) NOT NULL,
      UNIQUE(retailer_id, chain_id), 
      CONSTRAINT chain_id_references_chain FOREIGN KEY (chain_id, chain_id) REFERENCES retailer (retailer_id, chain_id)
    );

By repeating chain_id in the foreign key, the constraint ensures that the chain_id of the retailer, the chain_id of the chain, and the retailer_id of the chain are all equal. This prevents the chain_id referencing a retailer that's not a chain:

    INSERT INTO RETAILER (retailer_id, chain_id, name) VALUES (1, 1, 'Bob''s convenience stores');
    INSERT INTO RETAILER (retailer_id, chain_id, name) VALUES (2, 1, 'Bob''s store 1');
    INSERT INTO RETAILER (retailer_id, chain_id, name) VALUES (3, 1, 'Bob''s store 2');
    INSERT INTO RETAILER (retailer_id, chain_id, name) VALUES (4, 2, 'Erroneous store');

    ERROR:  insert or update on table "retailer" violates foreign key constraint "chain_id_references_chain"
    DETAIL:  Key (chain_id, chain_id)=(2, 2) is not present in table "retailer".

People sitting around a circular table

This one is probably the most obscure, I did it purely as a thought experiment. How can we model people sitting around a circular table, where each person has a person to their left and to their right? One obvious way would be to have a position column between 1 and the number of people. However, this is ugly:

The database can't prevent gaps in the position
Code the get the next person needs to handle wrap around.

Instead, why not have a foreign key to the person on the left and right? We can use a multi-column foreign-key constraint to ensure the circular table invariant: the person on my left has me on their right, and the person on my right has me on their left.

    CREATE TABLE example (
      id integer PRIMARY KEY,
      -- the person to the left
      lft integer NOT NULL,
      -- the person to the right
      rgt integer NOT NULL,

      -- the person on my left must have me on their right
      UNIQUE(id, rgt),
      CONSTRAINT lft FOREIGN KEY (lft, id) REFERENCES example (id, rgt) DEFERRABLE INITIALLY DEFERRED,

      -- the person on my right must have me on their left
      UNIQUE(id, lft),
      CONSTRAINT rgt FOREIGN KEY (rgt, id) REFERENCES example (id, lft) DEFERRABLE INITIALLY DEFERRED 
    );

One person sitting alone:

1	INSERT INTO example (id, lft, rgt) VALUES (1, 1, 1);

Two people:

1	INSERT INTO example (id, lft, rgt) VALUES (2, 3, 3), (3, 2, 2);

Three people:

1	INSERT INTO example (id, lft, rgt) VALUES (4, 6, 5), (5, 4, 6), (6, 5, 4);

The constraint prevents us from breaking the circle in any way:

    INSERT INTO example (id, lft, rgt) VALUES (7, 1, 2);

    ERROR:  insert or update on table "example" violates foreign key constraint "lft"
    DETAIL:  Key (lft, id)=(1, 7) is not present in table "example".

I don't know if this would ever be useful in practice, but I think it's interesting.

Return to Articles & Guides