• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

course.header.alt.is_certifying

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 22/05/2019

Violation de contrainte d'unicité

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

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 capable de modifier ce comportement :

  • vous pourrez laisser l'insertion/la modification échouer, mais sans déclencher d'erreur ;

  • vous pourrez remplacer la ou les lignes qui existent 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

Les commandes d'insertion et modification possèdent une option : IGNORE, qui permet d'ignorer (tiens donc ! o_O ) 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".

INSERT INTO Espece (nom_courant, nom_latin, description)
VALUES ('Chien en peluche', 'Canis canis', 'Tout doux, propre et  silencieux');
ERROR 1062 (23000): Duplicate entry 'Canis canis' for key 'nom_latin'

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

INSERT IGNORE INTO Espece (nom_courant, nom_latin, description)
VALUES ('Chien en peluche', 'Canis canis', 'Tout doux, propre et  silencieux');
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.

UPDATE Espece SET nom_latin = 'Canis canis' WHERE nom_courant = 'Chat';
ERROR 1062 (23000): Duplicate entry 'Canis canis' for key 'nom_latin'
UPDATE IGNORE Espece SET nom_latin = 'Canis canis' WHERE nom_courant = 'Chat';
Query OK, 0 rows affected (0.01 sec)

Les chats sont toujours des "Felis 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 à la suite d'une insertion partielle due à une ligne qui ne respecte pas une contrainte d'unicité.

Syntaxe
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é (cela 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.

Mais que veut dire "remplacer l'ancienne ligne par la nouvelle" ?

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

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

id

sexe

date_naissance

nom

espece_id

53

M

2007-04-02 01:45:00

Spoutnik

3

É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.

INSERT INTO Animal (sexe, nom, date_naissance, espece_id)
VALUES ('F', 'Spoutnik', '2010-08-06 15:05:00', 3);
ERROR 1062 (23000): Duplicate entry 'Spoutnik-3' for key 'ind_uni_nom_espece_id'

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

REPLACE INTO Animal (sexe, nom, date_naissance, espece_id)
VALUES ('F', 'Spoutnik', '2010-08-06 15:05:00', 3);
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 !

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

id

sexe

date_naissance

nom

espece_id

63

F

2010-08-06 15:05:00

Spoutnik

3

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, puisque l'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é :

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.
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
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 que l'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 :

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 !

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

id

sexe

date_naissance

nom

espece_id

mere_id

pere_id

32

M

2009-07-26 11:52:00

Spoutnik

3

NULL

NULL

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

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';

id

sexe

date_naissance

nom

espece_id

mere_id

pere_id

32

M

2009-07-26 11:52:00

Spoutnik

3

52

NULL

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 :

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 INSERTUPDATE  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).

On commence à faire des choses plutôt sympathiques avec nos données, n'est-ce pas ? Cette partie a pu vous paraître un peu plus compliquée. Du coup, pour les prochaines, je vous prépare quelque chose de simple, et pourtant extrêmement utile !

Exemple de certificat de réussite
Exemple de certificat de réussite