escartefigue

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

Merci Philippe pour ce complément qui peut rendre bien service

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

Sevyc,

Un numéro d'enregistrement non utilisé ne peut pas être réaffecté une fois atteinte la limite int ?

Bonjour,

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.

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.

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 .

Bonjour

Excellent !

A cela, j'ajouterai qu'une partie du travail des DBA devrait être consacrée à la surveillance de ces Identifiants.

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 +

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!

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.

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

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