Violation de contrainte d'unicité

• Options des clés étrangères
• Sommaire
• Fonctions : nombres, chaînes et agrégats

Lorsque vous insérez ou modifiez une ligne dans une table, différents événements en relation avec les clés et les index peuvent se produire.

• L'insertion/la modification peut réussir, c'est évidemment le mieux.
• L'insertion/la modification peut échouer parce qu'une contrainte de clé secondaire n'est pas respectée.
• L'insertion/la modification peut échouer parce qu'une contrainte d'unicité (clé primaire ou index UNIQUE) n'est pas respectée.

Nous allons ici nous intéresser à la troisième possibilité : ce qui arrive en cas de non-respect d'une contrainte d'unicité. Pour l'instant, dans ce cas-là, une erreur est déclenchée. À la fin de ce chapitre, vous serez capables de modifier ce comportement :

• vous pourrez laisser l'insertion/la modification échouer, mais sans déclencher d'erreur ;
• vous pourrez remplacer la(les) ligne(s) qui existe(nt) déjà par la ligne que vous essayez d'insérer (ne concerne que les requêtes d'insertion) ;
• enfin vous pourrez modifier la ligne qui existe déjà au lieu d'en insérer une nouvelle (ne concerne que les requêtes d'insertion).

• Ignorer les erreurs
• Remplacer l'ancienne ligne
• Modifier l'ancienne ligne

Ignorer les erreurs

Les commandes d'insertion et modification possèdent une option : IGNORE, qui permet d'ignorer (tiens donc ! ) l'insertion ou la modification si elle viole une contrainte d'unicité.

Insertion

Nous avons mis une contrainte d'unicité (sous la forme d'un index UNIQUE) sur la colonne nom_latin de la table Espece. Donc, si l'on essaye d'insérer la ligne suivante, une erreur sera déclenchée puisqu'il existe déjà une espèce dont le nom latin est "Canis canis".

1
2

INSERT INTO Espece (nom_courant, nom_latin, description)
VALUES ('Chien en peluche', 'Canis canis', 'Tout doux, propre et  silencieux');

1

ERROR 1062 (23000): Duplicate entry 'Canis canis' for key 'nom_latin'

Par contre, si l'on utilise le mot-clé IGNORE :

1
2

INSERT IGNORE INTO Espece (nom_courant, nom_latin, description)
VALUES ('Chien en peluche', 'Canis canis', 'Tout doux, propre et  silencieux');

1

Query OK, 0 rows affected (0.01 sec)

Plus d'erreur, la ligne n'a simplement pas été insérée.

Modification

Si l'on essaye de modifier l'espèce des chats, pour lui donner comme nom latin Canis canis, une erreur sera déclenchée, sauf si l'on ajoute l'option IGNORE.

1

UPDATE Espece SET nom_latin = 'Canis canis' WHERE nom_courant = 'Chat';

1

ERROR 1062 (23000): Duplicate entry 'Canis canis' for key 'nom_latin'

1

UPDATE IGNORE Espece SET nom_latin = 'Canis canis' WHERE nom_courant = 'Chat';

1

Query OK, 0 rows affected (0.01 sec)

Les chats sont toujours des "Felix silvestris" !

LOAD DATA INFILE

La même option est disponible avec la commande LOAD DATA INFILE, ce qui est plutôt pratique si vous voulez éviter de devoir traficoter votre fichier suite à une insertion partielle due à une ligne qui ne respecte pas une contrainte d'unicité.

Syntaxe

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

LOAD DATA [LOCAL] INFILE 'nom_fichier' IGNORE    -- IGNORE se place juste avant INTO, comme dans INSERT
INTO TABLE nom_table
[FIELDS
    [TERMINATED BY '\t']
    [ENCLOSED BY '']
    [ESCAPED BY '\\' ]
]
[LINES 
    [STARTING BY '']    
    [TERMINATED BY '\n']
]
[IGNORE nombre LINES]
[(nom_colonne,...)];

Remplacer l'ancienne ligne

Lorsque vous voulez insérer une ligne dans une table, vous pouvez utiliser la commande bien connue INSERT INTO, ou vous pouvez utiliser REPLACE INTO. La différence entre ces deux requêtes est la façon qu'elles ont de gérer les contraintes d'unicité (ça tombe bien, c'est le sujet de ce chapitre !).

Dans le cas d'une insertion qui enfreint une contrainte d'unicité, REPLACE ne va ni renvoyer une erreur, ni ignorer l'insertion (comme INSERT INTO [IGNORE]). REPLACE va, purement, simplement et violemment, remplacer l'ancienne ligne par la nouvelle.

Prenons par exemple Spoutnik la tortue, qui se trouve dans notre table Animal.

1

SELECT id, sexe, date_naissance, nom, espece_id FROM Animal WHERE nom = 'Spoutnik';

Étant donné que nous avons mis un index UNIQUE sur (nom, espece_id), il est absolument impossible d'avoir une autre tortue du nom de Spoutnik dans notre table. La requête suivante va donc échouer lamentablement.

1
2

INSERT INTO Animal (sexe, nom, date_naissance, espece_id)
VALUES ('F', 'Spoutnik', '2010-08-06 15:05:00', 3);

1

ERROR 1062 (23000): Duplicate entry 'Spoutnik-3' for key 'ind_uni_nom_espece_id'

Par contre, si on utilise REPLACE au lieu de INSERT :

1
2

REPLACE INTO Animal (sexe, nom, date_naissance, espece_id)
VALUES ('F', 'Spoutnik', '2010-08-06 15:05:00', 3);

1

Query OK, 2 rows affected (0.06 sec)

Pas d'erreur, mais vous pouvez voir que deux lignes ont été affectées par la commande. En effet, Spoutnik est mort, vive Spoutnik !

1

SELECT id, sexe, date_naissance, nom, espece_id FROM Animal WHERE nom = 'Spoutnik';

Comme vous voyez, nous n'avons toujours qu'une seule tortue du nom de Spoutnik, mais il ne s'agit plus du mâle né le 2 avril 2007 que nous avions précédemment, mais bien de la femelle née le 6 août 2010 que nous venons d'insérer avec REPLACE INTO. L'autre Spoutnik a été purement et simplement remplacé.

Attention cependant, quand je dis que l'ancien Spoutnik a été remplacé, j'utilise le terme "remplacé", car il s'agit de la traduction de REPLACE. Il s'agit cependant d'un abus de langage. En réalité, la ligne de l'ancien Spoutnik a été supprimée, et ensuite seulement, le nouveau Spoutnik a été inséré. C'est d'ailleurs pour cela que les deux Spoutnik n'ont pas du tout le même id.

Remplacement de plusieurs lignes

Pourquoi ai-je bien précisé qu'il ne s'agissait pas vraiment d'un remplacement, mais d'une suppression suivie d'une insertion ? Parce que ce comportement a des conséquences qu'il ne faut pas négliger !

Prenons par exemple une table sur laquelle existent plusieurs contraintes d'unicité. C'est le cas d'Animal, puisqu'on a cet index UNIQUE (nom, espece_id), ainsi que la clé primaire (l'id doit donc être unique aussi).

Nous allons insérer avec REPLACE une ligne qui viole les deux contraintes d'unicité :

1
2

REPLACE INTO Animal (id, sexe, nom, date_naissance, espece_id) -- Je donne moi-même un id, qui existe déjà !
VALUES (32, 'M', 'Spoutnik', '2009-07-26 11:52:00', 3);        -- Et Spoutnik est mon souffre-douleur du jour.

1

Query OK, 3 rows affected (0.05 sec)

Cette fois-ci, trois lignes ont été affectées. Trois ! Tout simplement, les deux lignes qui empêchaient l'insertion à cause des contraintes d'unicité ont été supprimées. La ligne qui avait l'id 32, ainsi que l'ancien Spoutnik ont été supprimés. Le nouveau Spoutnik a ensuite été inséré.

LOAD DATA INFILE

REPLACE est également disponible avec LOAD DATA INFILE. Le comportement est exactement le même.

Bien entendu, IGNORE et REPLACE ne peuvent pas être utilisés en même temps. C'est l'un ou l'autre.

Syntaxe

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

LOAD DATA [LOCAL] INFILE 'nom_fichier' REPLACE   -- se place au même endroit que IGNORE
INTO TABLE nom_table
[FIELDS
    [TERMINATED BY '\t']
    [ENCLOSED BY '']
    [ESCAPED BY '\\' ]
]
[LINES 
    [STARTING BY '']    
    [TERMINATED BY '\n']
]
[IGNORE nombre LINES]
[(nom_colonne,...)];

Modifier l'ancienne ligne

REPLACE supprime l'ancienne ligne (ou les anciennes lignes), puis insère la nouvelle. Mais parfois, ce qu'on veut, c'est bien modifier la ligne déjà existante. C'est possible grâce à la clause ON DUPLICATE KEY UPDATE de la commande INSERT.

Syntaxe

Voici donc la syntaxe de INSERT INTO avec cette fameuse clause :

1
2
3

INSERT INTO nom_table [(colonne1, colonne2, colonne3)]
VALUES (valeur1, valeur2, valeur3)
ON DUPLICATE KEY UPDATE colonne2 = valeur2 [, colonne3 = valeur3];

Donc, si une contrainte d'unicité est violée par la requête d'insertion, la clause ON DUPLICATE KEY va aller modifier les colonnes spécifiées dans la ligne déjà existante.

Exemple : revoici notre petit Spoutnik !

1
2
3

SELECT id, sexe, date_naissance, nom, espece_id, mere_id, pere_id 
FROM Animal 
WHERE nom = 'Spoutnik';

Essayons d'insérer une autre tortue du nom de Spoutnik, mais cette fois-ci avec la nouvelle clause.

1
2
3
4
5
6
7

INSERT INTO Animal (sexe, date_naissance, espece_id, nom, mere_id)
VALUES ('M', '2010-05-27 11:38:00', 3, 'Spoutnik', 52) -- date_naissance et mere_id sont différents du Spoutnik existant
ON DUPLICATE KEY UPDATE mere_id = 52;

SELECT id, sexe, date_naissance, nom, espece_id, mere_id, pere_id 
FROM Animal 
WHERE nom = 'Spoutnik';

Spoutnik est toujours là, mais il a désormais une mère ! Et son id n'a pas été modifié. Il s'agit donc bien d'une modification de la ligne, et non d'une suppression suivie d'une insertion. De même, sa date de naissance est restée la même puisque l'UPDATE ne portait que sur mere_id.

En fait, ce que nous avons fait était équivalent à la requête de modification suivante :

1
2
3
4

UPDATE Animal
SET mere_id = 52
WHERE nom = 'Spoutnik'
AND espece_id = 3;

Attention : plusieurs contraintes d'unicité sur la même table

Souvenez-vous, avec REPLACE, nous avons vu que s'il existait plusieurs contraintes d'unicité sur la même table, et que plusieurs lignes faisaient échouer l'insertion à cause de ces contraintes, REPLACE supprimait autant de lignes que nécessaire pour faire l'insertion.

Le comportement de ON DUPLICATE KEY est différent ! Dans le cas où plusieurs lignes seraient impliquées, seule une de ces lignes est modifiée (et impossible de prédire laquelle). Il faut donc à tout prix éviter d'utiliser cette clause quand plusieurs contraintes d'unicité pourraient être violées par l'insertion.

En résumé

• Le mot-clé IGNORE, utilisé dans des requêtes INSERT, UPDATE ou LOAD DATA, permet de ne pas déclencher d'erreur en cas de violation d'une contrainte d'unicité : la ligne posant problème ne sera simplement pas insérée/modifiée.
• Utiliser REPLACE au lieu de INSERT (ou dans LOAD DATA) supprime les lignes existantes qui provoquent une violation de la contrainte d'unicité à l'insertion, puis insère la nouvelle ligne.
• Il est possible, en ajoutant une clause ON DUPLICATE KEY UPDATE à une requête INSERT INTO, de provoquer soit une insertion (si aucune contrainte d'unicité n'est violée), soit une modification de certaines valeurs de la ligne déjà existante (dans le cas contraire).

• Options des clés étrangères
• Sommaire
• Fonctions : nombres, chaînes et agrégats