SGBD - Renumérotation des identifiants d'une base de données... l'éternelle question,

Un billet blog d'escartefigue

Voici le genre de questions très fréquemment posées sur ce forum et ailleurs :

ou encore

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