Tous les informaticiens le savent, la clef est l’élément indispensable, nécessaire à l’identification immédiate et directe de toute ligne d’information contenue dans une table. L’idée d’utiliser une clef numérique propre au modèle de données informatique est rapidement devenue un standard. Il reste néanmoins à trouver le bon algorithme pour la génération de la valeur de la clef, autrement dit son « calcul »...
Les qualités requises pour qu’un élément d’information soit candidat au poste de clef (souvent appelée clef primaire, ce qui est un pléonasme dont le but est de s’opposer terminologiquement au concept de clef, ou index secondaire, ou encore de clef étrangère) sont les suivantes :
Concision
Unicité
Stabilité
La concision, parce que, comme dit le proverbe : « ce qui se conçoit bien s’énonce clairement », c’est dire que la est attendue. Que dirait-on d’une enseigne de VPC dont les références des marchandises serait composées d’un code de 32 caractères qu’il faudrait communiquer au téléphone pour passer sa commande ?
Unicité car la clef doit permettre de retrouver l’ensemble des informations afférent à un élément individuel et non à un groupe ou un ensemble. Quel brouhaha cacophonique si un même numéro de téléphone était attribué a différents abonnés !
Stabilité enfin, puisqu’il convient de minimiser les efforts de mémorisation ! Combien de fois pestons nous contre ces gens ou ces entreprises qui ont la manie de changer de coordonnées (téléphone, fax, adresse…) ou encore qui « relookent » leur référentiel régulièrement en changeant la typologie et les valeurs de leurs nomenclatures ?
Au sens informatique, une clef parfaite serait donc une clef d’un coût nul pour son stockage, universellement unique (même d’une entreprise à l’autre, ce que commence à faire certains systèmes informatiques) et ne subissant aucune modification, altération ou suppression, même après la mort de l’élément qu’elle représente.
Plus modestement, un bonne clef serait donc d’un coût négligeable, par exemple une valeur numérique entière, bien entendu unique, au moins au sein du modèle qu’elle représente et enfin attribuée une fois pour toute durant la vie entière de l’objet qu'elle identifie.
2. Clef naturelle ou clef numérique ?
Le vieux débat qui consiste à se demander s’il faut utiliser une clef naturelle, c’est à dire parmi les colonnes d’information de la table, ou bine une clef « artificielle » propre au système informatique est au moins aussi vieille que l’informatique elle même. En effet, l’utilisation de clefs est une nécessité que l’on trouve dès l’origine des fichiers informatique, et bon nombre de références aujourd’hui bien connues, dérivent de cette notion : code postal, code RCS des entreprises, immatriculation des véhicules à moteurs, numéro de sécurité sociale… autant d’information créées spécifiquement pour des besoins de recensement et de statistique, par le législateur. La question est donc, faut-il utiliser l’une de ces références dans un modèle de données informatique ou convient-il d’en ajouter une nouvelle, propre au système d’information ?
Le débat peut être tranché rapidement en analysant le « comportement » de ces clefs dans la vie de l’information et plus généralement des éléments « vivants » qui se cachent derrières les concepts que l’informatique modélise.
Prenons le numéro de sécurité sociale : immuable, il est attribué « à vie ». Constitue t-il une bonne clef pour un fichier informatique ? Hélas non ! Un étranger, c’est à dire une personne ne relevant pas du système de l’emploi en France, ne saurait avoir une telle référence. Quand bien même il viendrait à y travailler un numéro provisoire lui serait attribué, en attendant qu’il obtienne une référence définitive de la part de l’administration.
Comme autre exemple, la carte grise et donc l’immatriculation, semble une clef idéale pour la modélisation d’une flotte de véhicules terrestre à moteur. Hélas il faut se rappeler qu’avant d’obtenir une immatriculation définitive par la préfecture, c’est le garagiste qui, sous contrôle de l’administration, délivre un certificat provisoire. Mais il y a pire… Au début des années quatre vingt le département de la Marne en région Champagne, avait la taxe fiscale la plus favorable en matière des vignettes automobile. Les sociétés de location de véhicules se mirent à faire immatriculer tous leurs véhicules dans ce département pour faire un pied de nez au ministre des finances. Ledit ministre, alors courroucé, prononça une loi afin de récupérer la partie de la manne fiscale espérée, qui avait disparue à cause du plan machiavélique des loueurs de voitures. Combien de véhicules ont-ils ainsi vu leur immatriculation changer au cours de leur possession ?
On pourrait aussi citer le cas des sociétés, rachetées, fusionnées, absorbées, holdinguisées ou encore exportées, dont le numéro du registre des sociétés, censé être immuable, a pourtant bien été modifié ou supprimé !
Il semble donc évident que l’utilisation d’une clef naturelle est une fort mauvaise idée ! Et par voie de conséquence, un bon vieux numéro fait parfaitement l’affaire pour la plupart des cas. Encore faut-il savoir comment en générer sa valeur !
3. L’auto incrément
Calculer toute nouvelle valeur de clef, lorsqu’elle se révèle être de nature numérique et si possible entière, est d’une facilité déconcertante. Il s’agit ni plus ni moins que d’incrémenter, c’est à dire rajouter une unité, à la plus haute des valeurs déjà attribué. Lorsque ce mécanisme est automatisé, on l’appelle, tout simplement, auto incrément. Si la difficulté ne réside pas dans le calcul lui même, le point rédhibitoire se situe en fait dans l’endroit ou le code réalisant cet auto incrément est implanté !
3.1. Une fausse bonne idée
La manière la plus simple de réaliser un tel mécanisme consiste à trouver le maximum déjà attribué au sein des données de l’ensemble concerné et d’y ajouter la valeur « un ».
Par exemple :
SELECTMAX(LaColonneClef) + 1
FROM MaTable
Dont la requête SQL renvoi précisément cette valeur.
Mais cette façon de procéder possède deux limites très contraignantes :
s’assurer que la clef soit TOUJOURS utilisée et non abandonnée
s’interdire la concurrence des utilisateurs
En effet un tel mécanisme n'est en aucun cas capable de réaliser proprement sa tâche si une sauvegarde ou un archivage peut être réimplanté, ou encore si l'application est utilisée par différents utilisateurs simultanément.
Étudions le cas de l'archivage... A un instant donné, la table MaTable contient la clef 48 attribué à Monsieur Paul DUFOUR qui est la plus forte clef en valeur. Il est procédé à un archivage. Puis la ligne de référence 48 est supprimée de la table. La plus forte référence de clef devient donc 47. A nouveau on génère une clef par le mécanisme ci dessus pour saisir les données de Monsieur Alain DUMAS, et cette clef vaut à nouveau 48 (47 + 1). Pour une raison ou pour une autre, on a besoin de reprendre les lignes archivées de cette table… Hélas il est impossible de reprendre l'archive puisque la clef 48 de DUFOUR a été ré attribué à DUMAS ! Bien entendu le cas de la reprise d'une sauvegarde conduit à des problèmes similaires...
Mais le cas le plus grave réside dans le multi-utilisateur... L'utilisateur A, procède à l'acquisition d'une clef en vue de saisir les données afférentes à Monsieur Gilles LEBLANC. Il lui est attribué la clef 53 puisque la dernière valeur de clef stockée dans la table MaTable est 52. Quelques instants plus tard, l'utilisateur B convient de saisir les informations de Monsieur Pierre LENOIR et se fait attribuer lui aussi une clef. Vous aurez deviné que A n'a pas encore terminé de saisir ses informations et que par conséquent l'utilisateur B se voit attribuer la même valeur de clef que l'utilisateur A, à savoir 53. Pour peu que le code ait été écrit un peu à la "va-vite" il n'est pas impossible de penser que A puisse valider des informations relative a LEBLANC dans une table fille, alors que B vient de valider les informations de Monsieur LENOIR. C'est ainsi que des informations d'un individu sont attribué à un autre, chose récemment vu au plus haut niveau de notre désespérante administration en matière d'avis d'imposition...
Nous retiendrons en conclusion qu'une telle solution est à proscrire. Mais elle nous livre deux éléments intéressant pour mener la réflexion qui nous mettra sur la voie de l'algorithme le plus sûr :
une clef utilisée ne doit jamais être ré attribué, autrement dit : toute clef consommée est perdue
pendant toute la durée qui courre du calcul de la nouvelle clef à son insertion dans la table aucun utilisateur du système informatique ne doit pouvoir en prendre une autre de même valeur
Par conséquent, et paradoxalement, on ne peut pas compter sur la table elle même pour connaître la valeur a attribuer à la nouvelle clef à calculer.
Il en découle une évidence : le mécanisme de calcul de la nouvelle clef doit être EXTERNE à la table !
3.2. La solution : une table des clefs
Une des solutions éprouvées, consiste à réaliser au sein même de la base de données, une table contenant la dernière valeur attribuée de chacune des clefs des tables.
Il n'y a plus qu'à réaliser le mécanisme de calcul de la clef, calcul qui doit se faire conjointement avec la mise à jour de la table des clefs, d'où l'idée d'une transaction.
UPDATE LesClefs
SET ValeurClefs = ValeurClefs + 1
WHERE NomTable = 'MaTable'
SELECT ValeurClef
FROM LesClefs
WHERE NomTable = 'MaTable'
COMMIT
Par défaut la norme SQL considère que le premier ordre SQL passé démarre une transaction, C'est pourquoi dans notre code nous n'avons pas placé un ordre BEGIN TRANSACTION, par ailleurs inexistant dans la norme ! Attention cependant, car ceci n'est pas toujours vrai dans les SGBDR modernes qui pratiquent la plupart du temps l'auto-commit...
Bien entendu cela peut être placé dans une procédure stockée qui peut réaliser l'ensemble des opérations, voire même créer la table si d'aventure cette dernière n'existait pas. Un tel exemple est reproduit ci dessous avec un raffinement qui consiste à laisser la possibilité de choisir la colonne qui doit être auto-incrémentée, en acceptant même l'idée de laisser la possibilité à plusieurs colonnes d'être auto incrémentées.
Le code a été écrit pour SQL Server v7 en langage Transact SQL :
CREATE PROCEDURE SP_SYS_DB_CALC_NEW_KEY
@NOM_TABLE VARCHAR(128)
AS--*******************************************************-- PROCÉDURE STOCKÉE DE CALCUL DE CLEF AUTO INCRÉMENTÉ //--///////////////////////////////////////////////////////-- Frédéric BROUARD - 2001-09-14 //--///////////////////////////////////////////////////////-- chaîne de requêteDECLARE @STR_QRY VARCHAR(8000)
-- booléen pour connaître si les données existent-- ou sont à créerDECLARE @OK INTEGERSET @OK = 0
-- valeur devant être retournéeDECLARE @maxcle integerSET @maxcle = -1
SETNOCOUNTON-- ON DEMARRE LA TRANSACTIONSET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION SET_KEY
-- on regarde si la table des clefs existeSELECT @OK = COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SYS_DB_MASTERKEY'
-- si elle n'existe pas il faut la créerIF @OK = 0
BEGINCREATETABLE SYS_DB_MASTERKEY
(MSK_TABLE_NAME CHAR(128) NOTNULL,
MSK_LAST_VALUE INTEGERNOTNULLDEFAULT 0,
CONSTRAINT MSK_KEY PRIMARYKEY
(MSK_TABLE_NAME))
-- création des indexCREATEINDEX MSK_PK ON SYS_DB_MASTERKEY
(MSK_TABLE_NAME)
END-- on regarde si la table concernée existeSET @OK = 0
SELECT @OK = COUNT(*)
FROM SYS_DB_MASTERKEY
WHERE MSK_TABLE_NAME = @NOM_TABLE
-- elle n'existe pas, on l'y insèreIF @OK = 0
BEGINSET @STR_QRY =
'INSERTINTO SYS_DB_MASTERKEY ' +
'SELECT ''' + @NOM_TABLE +
''', MAX(' + @NOM_CHAMP +') ' +
' FROM ' + @NOM_TABLE
EXEC(@STR_QRY)
END-- calcule de la nouvelle clefSELECT @maxcle = MSK_LAST_VALUE +1
FROM SYS_DB_MASTERKEY
WHERE MSK_TABLE_NAME = @NOM_TABLE
-- mise à jour de la nouvelle clef dans la table des clefsUPDATE SYS_DB_MASTERKEY
SET MSK_LAST_VALUE = @maxcle
WHERE MSK_TABLE_NAME = @NOM_TABLE
-- renvoi de la valeur de la clefSELECT @maxcle
SETNOCOUNTOFFCOMMIT TRANSACTION SET_KEY
Une telle procédure stockée peut être appelé dans un trigger, comme depuis un langage hôte.
Voici un exemple pour WinDev :
fonction SQLIdentAuto(NomFic)
/////////////////////////////////////////////////////////
// Calcule le prochain identifiant automatique //
// auto-incrémenté //
// Rend -1 en cas de problème //
/////////////////////////////////////////////////////////
IdentAuto est un entier long
si SQLExec( "SP_SYS_DB_CALC_NEW_KEY '"
+NomFic+"'","MAXREQ")alors
SqlAssocie("MAXREQ",IdentAuto)
SQLPremier("MAXREQ")
sinon
IdentAuto = -1
erreur("Erreur incrément")
fin
renvoyer IdentAuto
SQLFerme("MAXREQ")
fin
4. Les mécanismes internes aux SGBDR
Les éditeurs de SGBDR ont proposé diverses solutions pour l'auto incrémentation des tables. Ainsi des SGBDR comme Paradox ou Access proposent une colonnes de type AUTOINC c'est à dire un entier dont la valeur est calculé à chaque nouvelle insertion. Bien entendu chaque nouvelle valeur de clef calculé est considérée comme consommée, tant est si bien que l'on ne récupère jamais les éventuels "trous".
Certains SGBDR proposent d'utiliser un objet particulier de la base constitué par un "générateur" capable de fournir un entier auto incrémenté à chaque appel. C'est le cas d'Oracle et d'InterBase (Borland).
Voici un exemple pour le SGBDR InterBase de BORLAND :
Création du générateur :
CREATE GENERATOR monGenerateur TO 2301;
Qui indique de réserver un espace pour stocker la valeur de l'auto incrément de nom monGenerateur, commençant par la valeur 2301.
On peut alors exiger que toutes les clefs soient calculées par ce mécanisme notamment en utilisant un déclencheur, ou bien ne l'utiliser que sciemment par exemple lors d'une insertion de données.
Voici un exemple de trigger qui réalise ce mécanisme :
CREATETRIGGER AUTOINC_CLI FOR maTable
BEFOREINSERTASBEGIN
NEW.laClef = GEN_ID(monGenerateur, 1);
END
NEW.laClef est la valeur de la colonne après passage dans le trigger et GEN_ID une fonction appelant le générateur et calculant son incrément.
ATTENTION : si vous voulez connaître la valeur de la clef après une insertion il ne faut surtout pas interroger le générateur, car ce dernier peut déjà avoir été appelé par un processus concurrent pour une nouvelle insertion. Dans ce cas il faut générer la clef avant l'insertion et hors trigger :
Malheureusement ceci n'est pas possible dans SQL Server v7, car ce SGBDR ne supporte pas les triggers BEFORE et AFTER ! Conscient du problème, l'éditeur a cependant fournit assez tôt un mécanisme d'auto génération de clef incrémenté, définissable lors de la création de la table, à l'aide du mot clef IDENTITY[(valeur_initiale, incrément)]. Les conditions d'utilisation sont alors les suivantes :
par défaut valeur initiale et incrément valent 1
une seule colonne de chaque table peut accepter une telle contrainte
il est possible de débrancher ou rebrancher ce compteur en manipulant la valeur de la variable IDENTITY_INSERT (ON ou OFF)
il est possible de connaître la dernière valeur de clef insérée dans la table à l'aide de la variable @@IDENTITY
Exemple pour SQL Server v7 :
-- Création d'une table avec colonne auto incrémentée :CREATETABLE maTable
(LaClef INTEGER IDENTITY(6852,1) NOTNULLPRIMARYKEY,
UneColonne VARCHAR(32))
-- Récupération de la dernière valeur insérée :INSERTINTO maTable (UneColonne) VALUES ('exemple')
SELECT @@IDENTITY
----------------------------------------
6852
-- Débranchement de l'autoincrément pour insertion manuelle :SET IDENTITY_INSERT maTable ONINSERTINTO maTable (LaClef, UneColonne)
VALUES (623, 'exemple 2')
INSERTINTO maTable (LaClef, UneColonne)
VALUES (998877, 'exemple 3')
SET IDENTITY_INSERT maTable OFF-- Vérification INSERTINTO maTable (UneColonne) VALUES ('exemple 4')
SELECT * FROM maTable
LaClef UneColonne
----------- --------------------------------
623 exemple 2
6852 exemple
998877 exemple 3
998878 exemple 4
5. CONCLUSION
S'il semble que les mécanismes spécifiques fournis par les éditeurs de SGBDR soient plus performants que la technique de la table des clefs, la mesure de cette performance est en revanche assez faible. D'autre part, la méthode à base de table des clefs est, elle, portable dans tous les SGBDR sans grandes modifications. De plus quelques raffinements peuvent être apportés à cette méthode en stockant par exemple la valeur de départ et l'incrément afin de répondre à tous les usages.
On choisira donc la méthode à base de table des clefs dans l'éventualité d'une évolution du SGBDR et les méthodes spécifiques des SGBDR si l'on recherche la performance à tous prix.
6. A lire sur le sujet
L'excellent article de Peter Gulutzan dans dbazine à l'URL : http://www.dbazine.com/gulutzan4.html Qui nous signale que les deux systèmes (séquence et identité) seront normalisés dans la version SQL de 2003 !
