D'un premier abord, il peut paraître saugrenu de définir des références circulaires entre deux tables d'une base de données. Cependant, dans certains cas, ça peut s'avérer fort pratique. Imaginons par exemple le cas où vous devez stocker des machines à état dans votre base de données et que chacune de ces machines a une liste d'états qui lui est propre. En toute logique, vous avez donc une table avec les machines et une autre avec ses états possibles :

CREATE TABLE state_machines (
    id      uuid PRIMARY KEY,
    name    varchar(512) NOT NULL
);

CREATE TABLE states (
    id          uuid PRIMARY KEY,
    machine_id  uuid NOT NULL REFERENCES state_machines ON DELETE CASCADE,
    name        varchar(512) NOT NULL
);

C'est un bon début, mais maintenant il nous faut stocker l'état actuel de chaque machine. Pour ceci, votre collègue Jean-Kévin a une idée : il suffit d'ajouter un champ booléen is_current dans la table states afin d'indiquer si l'état en question est l'état actuel de la machine ou non.

CREATE TABLE states (
    id          uuid PRIMARY KEY,
    machine_id  uuid NOT NULL REFERENCES state_machines ON DELETE CASCADE,
    name        varchar(512) NOT NULL,
    is_current  boolean NOT NULL DEFAULT false
);

Ça fonctionne, mais il n'y a pas le moindre test de cohérence au niveau de la base de données qui garantisse chaque chaque machine dispose d'un et d'un seul état actuel. Du coup, si le programme qui l'utilise n'est pas parfaitement bien développé, il est possible que l'on se retrouve avec des machines disposant de plusieurs états ou bien d'aucun, ce qui n'a pas de sens.

INSERT INTO state_machines (id, name) VALUES
('adde3639-288e-4c9c-ab03-1d8d8836acc5', 'machine de test 1'),
('8bfce180-6056-4142-9e69-2b84701a9727', 'machine de test 2');
-- INSERT 0 2
-- Time: 4.236 ms

INSERT INTO states (id, machine_id, name, is_current) VALUES (
    'f847e4c7-435d-4670-9a53-29cceb068c54',
    'adde3639-288e-4c9c-ab03-1d8d8836acc5',
    'test état 1',
    true
), (
    '5cc4e9a3-0879-4b5b-811f-c15ef05e41ae',
    'adde3639-288e-4c9c-ab03-1d8d8836acc5',
    'test état 2',
    false
), (
    '95bb07b5-c930-4fc3-a846-758edab027c1',
    'adde3639-288e-4c9c-ab03-1d8d8836acc5',
    'test état 3',
    false
);
-- INSERT 0 3
-- Time: 5.333 ms

SELECT state_machines.id AS id, state_machines.name AS machine_name, states.name AS current_state
FROM state_machines INNER JOIN states ON state_machines.id = states.machine_id
WHERE is_current IS TRUE AND state_machines.id = 'adde3639-288e-4c9c-ab03-1d8d8836acc5';
--                   id                  |   machine_name    | current_state
-- --------------------------------------+-------------------+---------------
--  adde3639-288e-4c9c-ab03-1d8d8836acc5 | machine de test 1 | test état 1
-- (1 row)
--
-- Time: 0.672 ms

SELECT state_machines.id AS id, state_machines.name AS machine_name, states.name AS current_state
FROM state_machines INNER JOIN states ON state_machines.id = states.machine_id
WHERE is_current IS TRUE AND state_machines.id = '8bfce180-6056-4142-9e69-2b84701a9727';
--  id | machine_name | current_state
-- ----+--------------+---------------
-- (0 rows)
--
-- Time: 0.303 ms

Il serait sans doute plus prudent de ne pas utiliser de booléen mais d'ajouter à chaque machine une référence vers son état actuel :

CREATE TABLE state_machines (
    id                  uuid PRIMARY KEY,
    name                varchar(512) NOT NULL,
    current_state_id    uuid NOT NULL REFERENCES states ON DELETE RESTRICT
);

CREATE TABLE states (
    id          uuid PRIMARY KEY,
    machine_id  uuid NOT NULL REFERENCES state_machines ON DELETE CASCADE,
    name        varchar(512) NOT NULL
);
-- ERROR:  relation "states" does not exist
-- Time: 8.546 ms
-- ERROR:  relation "state_machines" does not exist
-- Time: 4.968 ms

Et là, c'est le drame ! La table state_machines a une référence sur la table states qui elle-même a une référence sur la table state_machines. Il est donc impossible d'en créer une avant l'autre. Heureusement, il nous est possible de n'ajouter la contrainte de clé étrangère de la table state_machines qu'une fois la table states créée :

CREATE TABLE state_machines (
    id                  uuid PRIMARY KEY,
    name                varchar(512) NOT NULL,
    current_state_id    uuid NOT NULL
);

CREATE TABLE states (
    id          uuid PRIMARY KEY,
    machine_id  uuid NOT NULL REFERENCES state_machines ON DELETE CASCADE,
    name        varchar(512) NOT NULL
);

ALTER TABLE state_machines
    ADD FOREIGN KEY (current_state_id)
        REFERENCES states
        ON DELETE RESTRICT;

C'est mieux, la création des tables fonctionne, mais malheureusement nous ne sommes pas encore sortis de l'auberge. En effet, le même problème se pose pour l'ajout d'une nouvelle machine car cela nécessite au moins un état, qui nécessite lui-même la machine en question.

C'est là qu'intervient la magie de PostgreSQL : il nous est possible, lors de la création de la contrainte de clé étrangère, de préciser que cette dernière ne doit être vérifiée qu'à la fin de la transaction en cours. Ceci s'effectue avec le mot-clé DEFERRABLE :

ALTER TABLE state_machines
    ADD FOREIGN KEY (current_state_id)
        REFERENCES states
        ON DELETE RESTRICT
        DEFERRABLE INITIALLY DEFERRED;

Ainsi, il nous est possible d'ajouter une machine avec ses différents états :

BEGIN;

INSERT INTO state_machines (id, name, current_state_id) VALUES (
    '35adfafa-c4f1-4637-877b-4f0dde597f5a',
    'Ma machine',
    '37b5969b-ad26-4952-b85c-e67ac0e27b6a'
);

INSERT INTO states (id, machine_id, name) VALUES (
    '4c41898b-0c0d-4595-a270-82b15812a42a',
    '35adfafa-c4f1-4637-877b-4f0dde597f5a',
    'état 1'
), (
    '37b5969b-ad26-4952-b85c-e67ac0e27b6a',
    '35adfafa-c4f1-4637-877b-4f0dde597f5a',
    'état 2'
), (
    'd81765e7-543c-4c82-9fd4-5716c24db3fd',
    '35adfafa-c4f1-4637-877b-4f0dde597f5a',
    'état 3'
);

COMMIT;

Nous avons ainsi réussi, dans une même transaction, à insérer notre machine, ses états et son état actuel. Ceci tout en ayant une contrainte au niveau de la base de données garantissant qu'une machine dispose toujours d'un et un seul état.

Notons que l'utilisation d'un UUID en index facilite grandement la chose de part sa possibilité d'être généré par l'application. Si ça n'avait pas été le cas, il aurait fallu :

1. utiliser une valeur temporaire pour current_state_id lors de l'insertion de la machine ;
2. récupérer l'id de la machine nouvellement créée afin de l'utiliser pour créer les états ;
3. récupérer l'id de l'état actuel ;
4. modifier la machine afin de remplacer la valeur temporaire par le véritable id de l'état actuel.

Pour plus d'information sur la déférabilité des contraintes dans PostgreSQL, vous pouvez consulter la documentation de SET CONSTRAINTS et de CREATE TABLE.