Pour empêcher un identifiant d’article d’apparaître plusieurs fois dans l’une ou l’autre de deux colonnes de référence, placez les deux rôles dans une table de références avec un identifiant d’article unique. Si chaque ligne autorise exactement une colonne renseignée, un index d’expression unique est une option plus simple.

Ce tutoriel s’adresse à un développeur travaillant dans Terminal sous macOS avec PostgreSQL et node-postgres. L’objectif est d’imposer un pool commun de références d’articles : dès qu’un article est référencé dans un rôle, toute autre référence à cet article doit échouer. Toutes les lignes d’exemple sont fictives.

Que signifie l’unicité dans les deux colonnes ?

Supposons que les deux colonnes référencent le même catalogue d’articles. La règle visée est que chaque identifiant d’article non nul apparaisse au plus une fois dans toutes les positions de référence, y compris les deux positions d’une même ligne. Cette règle est plus stricte que de rendre chaque colonne unique séparément.

Lot Article principal Article secondaire Résultat selon la règle visée
1 101 102 Accepté
2 103 101 Rejeté : 101 apparaît déjà dans le lot 1
3 104 104 Rejeté : le même article occupe les deux positions

Un UNIQUE (primary_article_id, secondary_article_id) rend la combinaison unique. Des contraintes d’unicité séparées rendent chaque colonne unique indépendamment. Aucune de ces conceptions ne rejette la répétition entre colonnes des lots 1 et 2. PostgreSQL indique qu’un index unique multicolonne rejette les doublons lorsque toutes les valeurs indexées correspondent. Index uniques

Choisissez la conception en fonction de la forme de ligne autorisée avant d’écrire la requête d’insertion :

Forme de ligne requise Conception adaptée
Les deux références peuvent être renseignées Stocker les références sous forme de lignes ; rendre article_id unique
Exactement une référence doit être renseignée Imposer cette règle avec une contrainte de ligne ; créer un index unique sur COALESCE
Zéro ou une référence peut être renseignée Utiliser une contrainte d’au plus une référence et le même index d’expression
Les colonnes référencent des catalogues différents Définir d’abord si des identifiants numériques égaux représentent la même chose ; ce tutoriel suppose un catalogue commun

Comment imposer la règle lorsque les deux références sont autorisées ?

Représentez chaque référence par une ligne contenant un lot, un rôle et un identifiant d’article. Le rôle préserve la signification de la colonne d’origine. Une seule contrainte d’unicité sur article_id couvre alors les deux rôles.

Le schéma ci-dessous combine des contraintes PostgreSQL classiques : les clés étrangères exigent des lignes correspondantes dans le catalogue, la clé primaire composite autorise une référence par lot et par emplacement, et la contrainte d’unicité nommée n’autorise qu’une seule occurrence de chaque article. Ces mécanismes de contrainte sont décrits dans Contraintes.

Créer un exemple reproductible

Utilisez une base de données d’exemple locale vide avec PostgreSQL 14–18 et une commande psql disponible. Enregistrez la configuration complète suivante dans schema.sql. Elle utilise des tables classiques non partitionnées et ne nécessite aucune extension.

BEGIN;

CREATE TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE article_bundles (
    bundle_id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE bundle_refs (
    bundle_id integer NOT NULL
        REFERENCES article_bundles (bundle_id),
    slot text NOT NULL
        CHECK (slot IN ('primary', 'secondary')),
    article_id integer NOT NULL
        REFERENCES articles (article_id),
    PRIMARY KEY (bundle_id, slot),
    CONSTRAINT bundle_refs_article_once UNIQUE (article_id)
);

INSERT INTO articles (article_id, title) VALUES
    (101, 'Grid spacing'),
    (102, 'Type scale'),
    (103, 'Page margins'),
    (104, 'Caption layout'),
    (105, 'Colour palette');

INSERT INTO article_bundles (bundle_id, label)
VALUES (1, 'Opening notes');

INSERT INTO bundle_refs (bundle_id, slot, article_id) VALUES
    (1, 'primary', 101),
    (1, 'secondary', 102);

COMMIT;

Dans Terminal sous macOS, définissez PGDATABASE sur le nom de cette base de données d’exemple existante, puis exécutez le fichier. Cette commande suppose que vos paramètres de connexion locale fonctionnent déjà.

export PGDATABASE=article_demo
psql -v ON_ERROR_STOP=1 -f schema.sql

Cette conception autorise un lot à avoir zéro, une ou deux références. Elle impose l’unicité et un maximum de deux rôles ; elle n’exige pas que chaque lot ait les deux rôles. Il s’agit d’une exigence distincte. Une référence absente est représentée par l’absence d’une ligne.

Conserver la forme à deux colonnes pour la lecture

Les applications peuvent toujours récupérer une ligne par lot avec deux champs de référence. Exécutez cette requête après la configuration :

SELECT b.bundle_id,
       b.label,
       p.article_id AS primary_article_id,
       s.article_id AS secondary_article_id
FROM article_bundles AS b
LEFT JOIN bundle_refs AS p
  ON p.bundle_id = b.bundle_id AND p.slot = 'primary'
LEFT JOIN bundle_refs AS s
  ON s.bundle_id = b.bundle_id AND s.slot = 'secondary'
ORDER BY b.bundle_id;

Le résultat initial est le lot 1 avec les références 101 et 102. La clé primaire du schéma garantit que chaque jointure trouve au plus une référence pour son emplacement. Utilisez cette requête comme format de lecture de l’application plutôt que de conserver une deuxième copie des valeurs de référence.

Comment node-postgres doit-il insérer les deux références ?

Créez le lot et ses références au sein d’une même transaction. En cas de conflit sur une référence, annulez également le lot. node-postgres exige que chaque instruction d’une transaction utilise le même client emprunté au pool ; des appels séparés à pool.query() ne conviennent pas à cette séquence. Transactions

Dans un répertoire de projet local, installez le paquet documenté pg. Utilisez une version LTS actuelle de Node.js et enregistrez le programme ci-dessous dans demo.mjs. Exécutez-le après la configuration SQL, avec le même environnement de connexion. node-postgres documente à la fois l’installation et les connexions basées sur l’environnement.

npm install pg
node demo.mjs

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

async function addBundle(bundleId, label, primaryId, secondaryId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'INSERT INTO article_bundles (bundle_id, label) VALUES ($1, $2)',
      [bundleId, label]
    );

    await client.query(
      `INSERT INTO bundle_refs (bundle_id, slot, article_id)
       VALUES ($1, 'primary', $2), ($1, 'secondary', $3)`,
      [bundleId, primaryId, secondaryId]
    );

    await client.query('COMMIT');
    console.log(`Added bundle ${bundleId}`);
  } catch (error) {
    await client.query('ROLLBACK');

    if (
      error.code === '23505' &&
      error.constraint === 'bundle_refs_article_once'
    ) {
      console.log(
        `Bundle ${bundleId} rejected: an article is already referenced`
      );
      return;
    }

    throw error;
  } finally {
    client.release();
  }
}

try {
  await addBundle(2, 'Layout notes', 103, 104);
  await addBundle(3, 'Colour notes', 105, 101);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Le programme transmet les valeurs à l’aide de paramètres numérotés et d’un tableau de valeurs distinct, conformément à l’API Requêtes de node-postgres. Voici le résultat attendu lors de la première exécution :

Lot 2 ajouté
Lot 3 rejeté : un article est déjà référencé

La deuxième transaction tente de réutiliser l’article 101. Son annulation supprime le lot 3 et laisse l’article 105 sans référence. Seul le conflit d’unicité spécifiquement nommé affiche le message de doublon ; les autres erreurs sont propagées. PostgreSQL identifie une violation d’unicité avec le SQLSTATE 23505 et fournit les noms de contraintes séparément du texte du message. Codes d’erreur PostgreSQL

Exécutez à nouveau la requête de lecture précédente pour vérifier le résultat : les lots 1 et 2 doivent subsister, avec les références 101–104. Considérez cette démonstration comme à exécuter une seule fois par configuration vierge : si vous relancez le programme, vous rencontrerez la clé primaire déjà présente du lot 2.

Quand un index unique sur COALESCE suffit-il ?

Utilisez cette conception plus simple uniquement lorsqu’une ligne contient au plus une référence réelle. COALESCE renvoie son premier argument non nul ; ainsi, une première colonne renseignée masque la deuxième colonne à l’expression indexée. PostgreSQL exige également que ses arguments aient un type commun. Expressions conditionnelles

Par exemple, les lignes (101, 102) et (103, 101) produisent les valeurs d’expression 101 et 103. Un index unique sur ces valeurs accepte les deux, même si l’article 101 apparaît deux fois. Ce contre-exemple montre pourquoi l’expression nécessite une contrainte sur la forme des lignes.

Pour une règle exigeant exactement une référence, exécutez l’exemple alternatif suivant après avoir créé le catalogue articles précédent. Sa table distincte illustre un autre schéma, au lieu d’ajouter des références à bundle_refs.

CREATE TABLE single_article_refs (
    reference_id integer PRIMARY KEY,
    primary_article_id integer REFERENCES articles (article_id),
    secondary_article_id integer REFERENCES articles (article_id),
    CONSTRAINT exactly_one_article CHECK (
        (primary_article_id IS NULL)
        <> (secondary_article_id IS NULL)
    )
);

CREATE UNIQUE INDEX single_article_refs_article_once
ON single_article_refs (
    (COALESCE(primary_article_id, secondary_article_id))
);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (1, 101, NULL);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (2, NULL, 101)
ON CONFLICT (
    (COALESCE(primary_article_id, secondary_article_id))
)
DO NOTHING
RETURNING reference_id;

La contrainte vérifie deux résultats booléens non nuls et n’accepte que les cas où une colonne est nulle et l’autre renseignée. PostgreSQL prend en charge les contraintes qui portent sur plusieurs colonnes d’une même ligne. Contraintes PostgreSQL 14

L’index d’expression unique place la valeur renseignée de l’une ou l’autre colonne dans un même domaine de comparaison. Les index d’expression sont documentés dans la version actuelle de PostgreSQL et dans PostgreSQL 14 ; cette approche ne nécessite pas la syntaxe supplémentaire de traitement des valeurs nulles de PostgreSQL 15.

La deuxième insertion ne renvoie aucune ligne, car l’article 101 est déjà référencé. Une expression peut servir de cible ON CONFLICT, et RETURNING signale les lignes effectivement insérées ou mises à jour. Interprétez un résultat vide comme une insertion ignorée. Le nom de l’index d’expression n’est pas un nom de contrainte de table utilisable avec ON CONFLICT ON CONSTRAINT. INSERT

Si les deux colonnes peuvent être vides, remplacez la contrainte « exactement une » par la condition de ligne suivante lors de la définition de la table. Conservez l’index d’expression. Le comportement par défaut de PostgreSQL en matière de valeurs nulles dans les index uniques autorise alors plusieurs lignes entièrement nulles. Index uniques

-- Alternative CHECK fragment for the CREATE TABLE definition:
CHECK (
    primary_article_id IS NULL
    OR secondary_article_id IS NULL
)

Comment détecter les doublons avant de modifier une table existante ?

Développez les deux colonnes en un seul flux avec UNION ALL, puis regroupez par identifiant d’article. Il est essentiel de conserver les doublons : un UNION ordinaire pourrait supprimer les éléments qui révèlent la répétition recherchée. Ce diagnostic complet utilise des données fictives et s’exécute indépendamment des schémas précédents.

WITH legacy_refs (bundle_id, primary_article_id, secondary_article_id) AS (
    VALUES
        (1, 101, 102),
        (2, 103, 101),
        (3, 104, 104)
),
expanded AS (
    SELECT bundle_id, 'primary' AS slot,
           primary_article_id AS article_id
    FROM legacy_refs
    WHERE primary_article_id IS NOT NULL

    UNION ALL

    SELECT bundle_id, 'secondary' AS slot,
           secondary_article_id AS article_id
    FROM legacy_refs
    WHERE secondary_article_id IS NOT NULL
)
SELECT article_id,
       count(*) AS occurrences,
       string_agg(
           bundle_id::text || ':' || slot,
           ', ' ORDER BY bundle_id, slot
       ) AS locations
FROM expanded
GROUP BY article_id
HAVING count(*) > 1
ORDER BY article_id;

Les identifiants en double attendus sont 101 et 104, avec deux occurrences chacun. Pour une table existante, remplacez le premier CTE par les colonnes réelles de la table. Examinez chaque occurrence signalée avant la migration ; la suppression automatique de lignes pourrait éliminer des références intentionnelles. Déplacez toutes les références dans la représentation choisie et orientez-y les écritures suivantes, car les tables alternatives distinctes de l’exemple ne partagent pas leur contrôle d’unicité.

Pourquoi la base de données doit-elle imposer la décision finale ?

Une vérification préalable peut expliquer un conflit existant, mais elle ne peut pas, à elle seule, garantir l’unicité. Deux opérations concurrentes peuvent toutes deux constater qu’une référence est inutilisée avant que l’une ou l’autre ne l’insère. L’implémentation de l’unicité de PostgreSQL vérifie les conflits lors de l’insertion dans l’index, attend si nécessaire les transactions concurrentes non validées et vérifie à nouveau le résultat. Vérifications d’unicité des index

Ne remplacez pas ce mécanisme par un CHECK qui interroge d’autres lignes. PostgreSQL indique explicitement que les contrôles interlignes ne sont pas pris en charge comme garantie continuellement maintenue. Utilisez des contraintes de ligne pour la forme autorisée des colonnes et des contraintes ou index d’unicité pour les références répétées. Contraintes

Le programme JavaScript a passé une vérification syntaxique, et le contre-exemple ainsi que la table de vérité « exactement une » ont été vérifiés de manière déterministe. Les exemples PostgreSQL ont été comparés à la documentation, mais n’ont pas été exécutés sur une base de données dans cet environnement de recherche.

Lorsque deux positions de référence renseignées sont autorisées, le schéma avec une table de références permet d’imposer directement la règle au moyen d’un identifiant d’article unique. Pour un choix exclusif entre colonnes, la conception vérifiée COALESCE conserve un schéma plus simple. Choisissez en fonction de la forme de ligne autorisée, puis laissez la base de données prendre la décision finale en matière d’unicité.

Sources