// Référence des erreurs PostgreSQL

PostgreSQL 23503 : violation de contrainte de clé étrangère

Une ligne référence une clé parente qui n'existe pas, ou vous avez supprimé ou modifié une ligne parente que des lignes enfants référencent toujours.

SQLSTATE
23503
Nom
foreign_key_violation
Catégorie
Violation de contrainte d'intégrité
Message d'erreur
insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

Ce que cela signifie

Une clé étrangère indique que chaque valeur dans la colonne enfant doit exister dans la colonne référencée (parente). PostgreSQL déclenche 23503 dans deux cas : lorsque vous insérez ou mettez à jour une ligne enfant dont la clé n'a pas de parent (is not present in table), et lorsque vous supprimez ou mettez à jour une ligne parente vers laquelle les lignes enfants pointent toujours (is still referenced from table).

Le DETAIL la ligne indique la valeur de la clé et l'autre table, pour que vous sachiez quelles lignes examiner.

Causes courantes

  • Insertion de lignes enfants avant leurs lignes parentes, par exemple en chargeant orders avant customers.
  • Une faute de frappe ou un identifiant obsolète dans la ligne enfant.
  • Supprimer une ligne parente qui a encore des enfants, sans ON DELETE CASCADE ou SET NULL sur la clé étrangère.
  • Mise à jour de la clé primaire d'un parent alors que des lignes enfants référencent l'ancienne valeur.
  • Deux transactions : l’une insère un enfant pour un parent qu’une autre vient de supprimer.

Comment le corriger

  1. Trouvez le parent manquant ou les enfants restants.

    SELECT * FROM customers WHERE id = 42;
    SELECT * FROM orders WHERE customer_id = 42;
  2. Insérer les parents d'abord, ou charger les deux tables dans une seule transaction avec la contrainte différée (elle doit être déclarée DEFERRABLE).

    ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY IMMEDIATE;
    BEGIN;
    SET CONSTRAINTS orders_customer_id_fkey DEFERRED;
    INSERT INTO orders (id, customer_id) VALUES (1, 42);
    INSERT INTO customers (id, name) VALUES (42, 'Ada');
    COMMIT;  -- checked here
  3. Lors de la suppression d'un parent, supprimer ou réaffecter d'abord ses enfants, ou changer la règle de suppression de la clé étrangère si c'est le comportement souhaité.

    ALTER TABLE orders
        DROP CONSTRAINT orders_customer_id_fkey,
        ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id)
            REFERENCES customers (id) ON DELETE CASCADE;
  4. Trouvez les orphelins avant d'ajouter une clé étrangère à des données existantes.

    SELECT o.*
    FROM orders o
    LEFT JOIN customers c ON c.id = o.customer_id
    WHERE o.customer_id IS NOT NULL AND c.id IS NULL;

Reproduisez-le en toute sécurité

CREATE TEMP TABLE p (id int PRIMARY KEY);
CREATE TEMP TABLE c (p_id int REFERENCES p (id));
INSERT INTO c VALUES (1);  -- ERROR: ... violates foreign key constraint "c_p_id_fkey"

Erreurs liées

Plus d'erreurs de violation de contrainte d'intégrité

Le code et le texte du message sont issus de Documentation PostgreSQL, annexe A : Codes d’erreur PostgreSQL. Parcourir la liste complète Liste des erreurs PostgreSQL ou Rechercher toutes les erreurs de base de données.

// moins d'erreurs, corrections plus rapides

Travailler sur PostgreSQL dans SQLly

Un client SQL natif pour macOS, Windows et Linux avec IntelliSense conscient du schéma, des garde-fous pour les instructions risquées et des messages d'erreur clairs, pour PostgreSQL et les autres bases de données que vous utilisez.