IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

escartefigue

[Actualité] Renumérotation des identifiants d'une base de données... l'éternelle question

Note : 2 votes pour une moyenne de 5,00.
par , 30/09/2022 à 15h21 (9992 Affichages)
Voici le genre de questions très fréquemment posées sur ce forum et ailleurs :

Citation Envoyé par un participant
Comment puis-je renuméroter les identifiants de ma table qui présentent des "trous"
ou encore

Citation Envoyé par un autre participant
J'aimerais réorganiser les identifiants de ma table .
Tout d'abord, un peu de sémantique
Dans une base de données, la réorganisation est une opération consistant à défragmenter les espaces physiques de stockage des données et des index.
Pour les pages de données, il s'agit de les ranger selon la séquence de rangement définie par l'index cluster
Pour les pages d'index, il s'agit de de les ranger selon l'ordre des colonnes de l'index
Dans les deux cas, l'opération de réorganisation ne modifie aucune valeur de quelque colonne que ce soit dans les tables !

À l'inverse, une renumérotation modifie la valeur d'une colonne

Il ne faut donc pas confondre réorganisation et renumérotation, ce sont deux opérations bien distinctes


Ensuite, quel est le rôle d'un identifiant ?

Un identifiant au niveau conceptuel (au stade du MCD donc), qui devient clef primaire au niveau SQL, n'a pour fonction que de permettre d'identifier de façon certaine une occurrence d'entité (au niveau conceptuel) et donc une ligne particulière dans une table (au niveau SQL).
Les caractéristiques d'une clef primaire sont d'être unique, stable, non "nullable" et irréductible.
  • Unique : comme son nom l'indique, chaque valeur ne peut exister qu'une seule fois ;
  • Stable : la valeur d'une PK (clef primaire) se propage dans les tables associées au travers des contraintes d'intégrité ;
  • Non "nullable" : la valeur est obligatoire, aucune PK ne peut être marquée "null" ;
  • Irréductible : une sous-partie de la PK ne peut suffire à garantir l'unicité.



Si l'on changeait la valeur d'un identifiant et donc d'une clef primaire (dite "PK" pour Primary Key), c'est le 2e point, la stabilité, qui pose problème : à cause du phénomène de cascade, l'envie autant compulsive qu'inutile de "boucher les trous", peut engendrer des millions, voire des milliards de mises à jour inutiles et mettre à plat la base de données.

Et tout ça pour quoi ?

Une valeur d'identifiant manquante peut être causée par
  • une ligne supprimée par un ordre DELETE ;
  • une insertion non commitée ;
  • un pas d'incrément différent de 1.


De plus, il faut savoir que la valeur des identifiants n'a pas de sens chronologique
  • on peut à tout moment forcer la valeur d'un identifiant dans l'ordre INSERT en en communiquant la valeur  ;
  • sur certains SGBD, les valeurs d'identifiants sont distribuées par paquet pour éviter de solliciter le moteur à chaque insertion, mais bien entendu, l'utilisation des identifiants dans chaque thread concurrent n'est pas obligatoirement choronologique, ainsi, l'identifiant de valeur 100 a très bien pu être commité (validé) après celui de valeur 120.


Enfin, boucher les trous serait un chantier permanent
Comme expliqué plus haut, il est normal que certaines valeurs d'identifiant soient absentes, si on voulait que ce ne soit pas le cas, il faudrait renuméroter en permanence les ID, plusieurs fois par jour même


Vous avez besoin d'un chrono unique et dont les valeurs sont contiguës ?
utilisez la fonction ROW_NUMBER(), elle est faite pour ça, mais par pitié, ne touchez pas aux identifiants.


Vous avez besoin de connaître la chronologie d'insertion des lignes dans une table
ajoutez une colonne d'horodatage fin, type timestamp, mais n'utilisez jamais les identifiants

Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Viadeo Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Twitter Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Google Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Facebook Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Digg Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Delicious Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog MySpace Envoyer le billet « Renumérotation des identifiants d'une base de données... l'éternelle question » dans le blog Yahoo

Catégories
Sans catégorie

Commentaires

  1. Avatar de CinePhil
    • |
    • permalink
    Excellent conseil de ne jamais toucher à un identifiant clé primaire d'une table.

    Petit ajout personnel pour ceux qui voudraient quand même avoir quelque part une idée d'ordonnancement des données...
    Si vous AVEZ BESOIN d'une information enregistrée en base de données pour que les données soient disponibles dans un certain ordre ET si cet ordre ne peut pas être obtenu naturellement par une requête, alors CRÉEZ UNE COLONNE DÉDIÉE à ça !

    Exemple...
    Soit une table qui permet d'enregistrer différentes catégories, en nombre relativement limité et qui sont enregistrées dans cet ordre, chronologiquement, et ayant donc un identifiant croissant du premier enregistré au dernier :
    1. Fantastique
    2. Science-fiction
    3. Aventure
    4. Historique
    5. Politique
    6. Féérique
    7. Biographie

    Cet ordre d'enregistrement ne vous convient pas pour l'affichage de la liste sur votre application. Un classement alphabétique ne vous convient pas non plus parce que vous souhaitez que les genres similaires soient regroupés dans la même partie de la liste : Aventure, Fantastique, Féérique, Science-Fiction.

    La solution est d'ajouter une colonne pour l'ordre que vous souhaitez :
    id - categorie - ordre
    1 - Fantastique - 2
    2 - Science-fiction - 4
    3- Aventure - 1
    4 - Historique - 5
    5 - Politique - 6
    6- Féérique - 3
    7- Biographie - 7

    Pour avoir la liste dans l'ordre, rien de plus simple :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT categorie, id
    FROM la_table
    ORDER BY ordre;

    C'est ce que je fais dans quasiment toutes mes tables de référence (catégories, genres, types, niveaux...)
  2. Avatar de escartefigue
    • |
    • permalink
    Merci Philippe pour ce complément qui peut rendre bien service
  3. Avatar de sevyc64
    • |
    • permalink
    Hello, je suis, ou plutôt devrais je dire, j'étais entièrement d'accord avec ce principe jusqu'à cet apm, on ne touche jamais à un identifiant.

    Sauf que voilà, ma table à une PK de type int, auto-incrément. Et depuis 2 jours c'est la grosse panique, l'identifiant à atteint les 2.4 milliards et quelques poussières soit la capacité max d'un int.
    mais je n'ai (si on peut dire) que 1.7 millions d'enregistrements dans ma table.

    Donc j'ai le choix,
    - soit je sort le bazooka pour tuer la mouche, je passe la PK en bitint, ainsi que les 15 FK qui la référence, et évidement tout le code métier derrière qui est lié plus ou moins directement à ces colonnes (en en oubliant forcément au passage).
    - soit je me la joue au scalpel, je mets la base KO et donc le site qui va avec, je fais sauter les contraintes, je renumérote le tout, je remets les contraintes en place et je réappuies sur ON.

    On ne touche jamais à un identifiant !
    Vraiment ?

    PS : Dans les 2 cas, c'est un gros chantier.
    Et faire ça une veille de we, quand on se retrouve momentanément seul à gérer 3 gros "incendies" en même temps, vive la vie de développeur
  4. Avatar de chrtophe
    • |
    • permalink
    Sevyc,

    Un numéro d'enregistrement non utilisé ne peut pas être réaffecté une fois atteinte la limite int ?
  5. Avatar de escartefigue
    • |
    • permalink
    Bonjour,

    Citation Envoyé par sevyc64
    On ne touche jamais à un identifiant !
    Vraiment ?
    C'est sans rapport avec mon propos : dans le cas d'une limite de renumérotation atteinte, on modifie le type (par exemple integer remplacé par bigint), mais on ne change pas les valeurs !
    Je maintiens que vouloir modifier la valeur d'un identifiant, surtout quand il s'agit de "boucher les trous de numérotation" est à la fois inutile, couteux et sans fin.

    Citation Envoyé par chrtophe
    Sevyc,

    Un numéro d'enregistrement non utilisé ne peut pas être réaffecté une fois atteinte la limite int ?
    Parler de "numéro d'enregistrement" est inadéquat, car il sous-entend une chronologie, ce qui n'est encore une fois pas le cas pour les identifiants attribués par le SGBD (identity, auto_increment, serial...)

    Ensuite, la réponse dépend du SGBD.
    Certains ne sont pas capables de boucler, d'autres le peuvent (par exemple DB2 le permet grâce au paramètre CYCLE facultatif). Dans tout les cas, une valeur ne peut être réutilisée que si elle n'existe pas déjà, unicité oblige. Il faut donc que la ligne ait été supprimée physiquement pour qu'une valeur d'identifiant déjà utilisée puisse l'être à nouveau.
    Mis à jour 10/10/2022 à 15h33 par escartefigue
  6. Avatar de sanderbe
    • |
    • permalink
    Bonsoir

    Votre article fait penser à la fonction "insert into" de MySQL . Quand on insert automatique un fichier plat, csv, txt ... l'autoincrement peut littéralement créer des "trous" dans la raquette de numérotation .
  7. Avatar de bernard59139
    • |
    • permalink
    Bonjour

    Excellent !

    A cela, j'ajouterai qu'une partie du travail des DBA devrait être consacrée à la surveillance de ces Identifiants.
  8. Avatar de SQLpro
    • |
    • permalink
    ATTENTION : si la contrainte d'unicité n'est pas supprimée, les SGBDR MySQL et PostGreSQL sont dans l'incapacité de mettre à jour la colonne, contrairement à IBM DB2, Oracle Database ou Microsoft SQL Server, parce qu'ils ne fonctionnent pas de manière ensembliste selon des règles de Codd et en particulier les règles 7 et 12...

    Pour PostGreSQL une solution de contournement que je trouve horrible est de prévoir une contrainte déferrable (aussi faut-il l'avoir pensée au niveau de la modélisation avant d'avoir le problème). Pour MySQL seule solution supprimer la contrainte d'IR de la FOREIGN KEY et la remettre après (un cierge est bienvenu !).

    Pour les colonnes pourvues de la propriété IDENTITY : cette propriété empêche l'UPDATE... Il faut alors supprimer la propriété IDENTITY de la colonne... C'est pas une mince affaire !

    A +
  9. Avatar de Eric80
    • |
    • permalink
    Citation Envoyé par sevyc64
    je passe la PK en bitint, ainsi que les 15 FK qui la référence, et évidement tout le code métier derrière qui est lié plus ou moins directement à ces colonnes (en en oubliant forcément au passage).
    tu touches là un autre pb. AMHA, le code métier ne devrait PAS dépendre du type des PK et FK!

    Les ORM font généralement un bon boulot d abstraction à ce niveau.
    Par ex, sur le EntityFramework de MS, la dernière fois que j ai touché à un gros projet métier l utilisant (bon il y a 10 ans...), les FK étaient définies par des relations: nulle trace de int out bigint dans le code métier!
    edit: ou plutot si, il y a une trace des ID int dans le Entity DataModel, mais celui ci peut etre regénéré en qques clics après modif du type, sans que cela change le reste du code, si le code s'est bien gardé de travailler avec les propriétés xxxID définies en Int32.
    donc oui, cela impose qd meme un redéploiement du code métier, ce qui peut etre bcp plus complexe que le coup de scalpel dans la DB.
    Mais avec le coup de scalpel, il faut ensuite prier que les ID supprimés et réutilisés ne créent pas des bugs + tard (un truc bien codé ne devrait PAS tomber là dedans, mais qui sait...). Et aussi que ces IDs ne soient pas stockés dans des logs ou je ne sais quoi qui pourraient créer de la confusion avec la nouvelle entité une fois l ID réutilisé...
    Bref, à moyen terme, le coût de redéploiement du code métier est peut être moins pire que de jouer aux sorciers dans la DB!
    Mis à jour 15/11/2022 à 20h48 par Eric80
  10. Avatar de Eric80
    • |
    • permalink
    Comme dit, je suis un peu out dans le design des bases SQL, passée à du stockage noSQL depuis.
    Mais est ce tjs pertinent de mettre des numéro comme IDENTITY donc comme PK et FK ?
    Il me semblait que mettre des GUID était bcp plus tranquille et était devenue la norme: les avantages et inconvénient (essentiellement performance) sont rappelés ici: https://dba.stackexchange.com/questi...-a-primary-key et https://stackoverflow.com/questions/...s-int-identity
    int semble tjs préféré par bcp, mais certains soulignent aussi d autres pb.

    J imagine que Int garde du sens pour du code métier assez "proche" de la DB, mais que ceux qui utilisent des ORM et des requètes générées ou distribuées seraient mieux servis par des GUID.
  11. Avatar de escartefigue
    • |
    • permalink
    Citation Envoyé par Eric80
    [...]
    Mais est ce tjs pertinent de mettre des numéro comme IDENTITY donc comme PK et FK ?
    Il me semblait que mettre des GUID était bcp plus tranquille et était devenue la norme: les avantages et inconvénient [...]
    Ce n'est pas le sujet de ce blog, je n'y répondrai donc pas en détail ici, mais c'est le plus souvent une mauvaise idée.
    Je vous recommande la lecture de ce billet de Frédéric Brouard sur cette question :
    https://blog.developpez.com/sqlpro/p...ent_le_verdict
  12. Avatar de Séb.
    • |
    • permalink
    Citation Envoyé par sanderbe
    Votre article fait penser à la fonction "insert into" de MySQL . Quand on insert automatique un fichier plat, csv, txt ... l'autoincrement peut littéralement créer des "trous" dans la raquette de numérotation .
    Cela dépend du paramétrage de MySQL, voir https://dev.mysql.com/doc/refman/8.0...-handling.html