Violation de la contrainte Unique Key avec SQL Server

Comment éviter l’erreur Violation de la contrainte Unique Key avec SQL Server ? Voici deux solutions différentes, rapides et faciles pour écrire une requête « UPDATE OR INSERT ». En d’autres termes, éviter l’erreur SQL Server « Cannot insert duplicate key in object », « The duplicate key value is » car la ligne en question existe déjà dans la table cible. En effet, une insertion d’une même clef provoque une erreur.

Avant de commencer, pour tester les deux solutions d’une manière pratique, exécuter ce script pour créer une table d’exemple et insérer deux lignes de données. La solution est un simple mixe d’une commande update et d’une commande insert.  

Comment éviter l’erreur de violation de la contrainte Unique KEY avec SQL Server ?

Dans cet exemple, ce code de création de la table VENTES est utilisé pour l’exemple INSERT or UPDATE. Et donc la mise à jour des lignes de la table cible.

-- Si la table existe déjà, alors on la supprime
IF exists (
  SELECT 1 FROM sys.objects
  WHERE  object_id = object_id(N'[dbo].[VENTES]') AND type in (N'U')
)
BEGIN  DROP TABLE [dbo].[VENTES]
END
GO

-- Creation de la table d'exemple avec la colonne MOIS déclarée comme UNIQUE
CREATE TABLE [dbo].[VENTES]
(
  [MOIS]	nvarchar(20) UNIQUE,
  [MONTANT]	numeric(5)
)
GO

-- Insertion des données pour l'exemple
INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES  ( N'Janvier', 1000);
INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES  ( N'Janvier', 2000);

Violation de la contrainte UNIQUE KEY ‘UQ__VENTES__*’. Impossible d’insérer la clé en double dans l’objet ‘dbo.VENTES’. La valeur de clé en double est (Janvier).

Le message d’erreur qui s’affiche ressemble à cela, avec une version SQL Server en Anglais.
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint Cannot insert duplicate key in object ‘dbo.VENTES’. The duplicate key value is (Janvier).
The statement has been terminated.

Deux approches Insert OR Update ou alors Update OR Insert avec pour contourner l’erreur Unique Key

La première solution pour faire un INSERT OR UPDATE avec Microsoft SQL Server se fait en deux étapes.

Premièrement, on test si la ligne à insérer existe dans la table, à l’aide de la fonction EXISTS. Ensuite en fonction du résultat, si la ligne existe alors on effectue un UPDATE pour mettre à jour la valeur, et si elle n’existe pas alors on lance un INSERT pour insérer une nouvelle ligne. En pratique on ne fait pas un INSERT OR UPDATE, mais plutôt un UPDATE OR INSERT.

IF EXISTS(SELECT * FROM dbo.VENTES WHERE MOIS = 'Janvier')
BEGIN
  UPDATE 	dbo.VENTES
  SET 	MONTANT = 2000
  WHERE 	MOIS = 'Janvier';
END
ELSE
BEGIN
  INSERT INTO dbo.VENTES ( MOIS, MONTANT ) 
  VALUES  ( N'Janvier', 2000);
END

Voici la deuxième solution, effectuer un UPDATE et vérifier les lignes mises à jour.

Deuxièmement, commencer par un UPDATE de la ligne. Ensuite, seulement si le nombre de lignes mises à jour est égal à 0 alors on exécute l’instruction INSERT.  Pour finir, Cette dernière insère une nouvelle ligne pour le mois de Janvier, qui est la clef.

UPDATE 	dbo.VENTES
SET 	MONTANT = 2000
WHERE 	MOIS = 'Janvier';

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO dbo.VENTES ( MOIS, MONTANT ) 
  VALUES  ( N'Janvier', 2000);
END;

 Enfin, cet article vous a présenté ici deux façons de vérifié l’existence de données avant insertion ou après une mise à jour. Pour aller plus loin, la mise à jour d’une autre ligne avec la valeur de la même colonne peut vous intéresser.