// Référence des erreurs PostgreSQL

PostgreSQL 23505 : la clé en double viole la contrainte d'unicité

Un INSERT ou UPDATE créerait une deuxième ligne avec la même valeur dans une colonne couverte par une clé primaire, une contrainte unique ou un index unique.

SQLSTATE
23505
Nom
unique_violation
Catégorie
Violation de contrainte d'intégrité
Message d'erreur
duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

Ce que cela signifie

PostgreSQL applique chaque clé primaire, UNIQUE contrainte et l'index unique au moment où une ligne est écrite. Si la ligne nouvelle ou modifiée a la même clé qu'une ligne qui existe déjà (ou qu'une autre transaction a écrite mais pas encore validée), l'instruction échoue avec 23505 et rien de ce qu'elle a modifié n'est conservé.

Le DETAIL la ligne nomme la colonne et la valeur en double, et le message nomme la contrainte ou l'index, ce qui vous dit exactement quelle règle a été enfreinte.

Causes courantes

  • Insertion d'une ligne dont la clé existe déjà, par exemple en relançant un import ou un script de seed.
  • A serial ou séquence d'identité qui est en retard par rapport aux données, généralement après que des lignes ont été chargées avec des identifiants explicites. L'identifiant par défaut suivant entre en collision avec une ligne existante : le classique Key (id)=(1) already exists.
  • Deux sessions insérant la même clé en même temps ; la deuxième attend la première et échoue dès que la première est validée.
  • Une UPDATE qui met une colonne unique à une valeur qu'une autre ligne possède déjà.

Comment le corriger

  1. Trouvez la ligne existante nommée dans la DETAIL ligne et décidez si les nouvelles données sont un doublon ou un enregistrement différent.

    SELECT * FROM users WHERE email = '[email protected]';
  2. Si l'écriture doit mettre à jour la ligne existante à la place, faites un upsert avec ON CONFLICT.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  3. Si les doublons doivent simplement être ignorés, utilisez ON CONFLICT DO NOTHING.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO NOTHING;
  4. Si le doublon concerne une serial ou un identifiant d'identité, avancez la séquence au-delà de la valeur existante la plus élevée.

    SELECT setval(pg_get_serial_sequence('users', 'id'),
                  (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);

Reproduisez-le en toute sécurité

CREATE TEMP TABLE t (id int PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (1);  -- ERROR: duplicate key value violates unique constraint "t_pkey"

Questions

Pourquoi l'insertion échoue-t-elle avec la clé dupliquée sur la colonne id alors que je n'ai jamais défini l'id ?

La séquence de la table est en retard par rapport aux données, généralement parce que des lignes ont été insérées avec des identifiants explicites ou restaurées sans la valeur de la séquence. Réinitialisez-la avec setval juste après MAX(id).

Est-ce que ON CONFLICT fonctionne avec n'importe quel index unique ?

Il fonctionne avec les colonnes d'un index unique ou d'une contrainte unique, ou avec ON CONFLICT ON CONSTRAINT name. Il nécessite PostgreSQL 9.5 ou une version ultérieure.

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.