Comment échanger les valeurs de ligne dans MySQL avec une contrainte unique sans violer l'intégrité ?

Échanger les valeurs de ligne dans MySQL tout en préservant la contrainte unique

Dans MySQL, la tâche d'échanger les valeurs de priorité entre deux lignes dans une table avec un une contrainte unique peut rencontrer des erreurs dues à des violations de contrainte. Pour comprendre le problème, examinons une instruction UPDATE typique :

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

Cette instruction entraîne cependant une erreur :

Error Code: 1062. Duplicate entry '3' for key 'priority_UNIQUE'

Nature du problème

MySQL traite les mises à jour différemment des autres SGBD. Il vérifie les violations de contraintes après chaque mise à jour de ligne, plutôt qu'une fois l'instruction entière terminée. Par conséquent, l'échange de valeurs viole directement la contrainte unique.

Alternatives sans valeurs fausses ni requêtes multiples

Malheureusement, il n'existe aucun moyen d'effectuer cette opération sans utiliser un valeur intermédiaire (fausse ou nulle) ou requêtes multiples dans MySQL. La contrainte unique impose l'unicité après chaque mise à jour de ligne, rendant l'échange direct impossible.

Suppression temporaire de la contrainte

Une option consiste à supprimer temporairement la contrainte unique, à exécuter l'opération d'échange , puis rajoutez la contrainte. Cependant, cette approche n'est pas recommandée car elle compromet l'intégrité des données.

Fausses valeurs et requêtes multiples

L'approche recommandée consiste à utiliser une fausse valeur (-3) pour temporairement maintenez la valeur de priorité de la première ligne et échangez-la avec la valeur de la deuxième ligne. Deux requêtes au sein d'une transaction sont requises :

START TRANSACTION ;
    UPDATE tasks 
    SET priority = 
      CASE
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
      END 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;
COMMIT ;

Ce processus garantit que la contrainte n'est pas violée lors de l'opération d'échange.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!