Clefs auto incrémentées

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

Article lu   fois.

L'auteur

Site personnelSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

1. Qualité d'une bonne clef

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 :

 
Sélectionnez

SELECT MAX(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.

Une telle table pourrait être la suivante :

 
Sélectionnez

CREATE TABLE LesClefs
(NomTable   CHAR(128) NOT NULL PRIMARY KEY,
 ValeurClef INTEGER   NOT NULL DEFAULT 0)

Elle pourrait contenir les éléments suivants :

 
Sélectionnez

NomTable                          ValeurClef
--------------------------------  -----------------
MaTable                           58
UneAutreTable                     1587
...

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.

 
Sélectionnez

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 :

 
Sélectionnez

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ête
DECLARE @STR_QRY VARCHAR(8000)

-- booléen pour connaître si les données existent
-- ou sont à créer
DECLARE @OK INTEGER
SET @OK = 0

-- valeur devant être retournée
DECLARE @maxcle integer
SET @maxcle = -1

SET NOCOUNT ON

-- ON DEMARRE LA TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION SET_KEY

-- on regarde si la table des clefs existe
SELECT @OK = COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SYS_DB_MASTERKEY'
-- si elle n'existe pas il faut la créer
IF @OK = 0
BEGIN
   CREATE TABLE SYS_DB_MASTERKEY
   (MSK_TABLE_NAME CHAR(128) NOT NULL,
    MSK_LAST_VALUE INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT MSK_KEY PRIMARY KEY
              (MSK_TABLE_NAME))
-- création des index
   CREATE INDEX MSK_PK ON SYS_DB_MASTERKEY
          (MSK_TABLE_NAME)
END

-- on regarde si la table concernée existe
SET @OK = 0

SELECT @OK = COUNT(*)
FROM SYS_DB_MASTERKEY
WHERE MSK_TABLE_NAME = @NOM_TABLE
-- elle n'existe pas, on l'y insère
IF @OK = 0
BEGIN
   SET @STR_QRY =
       'INSERT INTO SYS_DB_MASTERKEY ' +
       'SELECT ''' + @NOM_TABLE +
       ''', MAX(' + @NOM_CHAMP +') ' +
       ' FROM ' + @NOM_TABLE
   EXEC(@STR_QRY)
END

-- calcule de la nouvelle clef
SELECT @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 clefs
UPDATE SYS_DB_MASTERKEY
SET    MSK_LAST_VALUE = @maxcle
WHERE  MSK_TABLE_NAME = @NOM_TABLE
-- renvoi de la valeur de la clef
SELECT @maxcle

SET NOCOUNT OFF

COMMIT 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 :

 
Sélectionnez

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 :

 
Sélectionnez

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 :

 
Sélectionnez

CREATE TRIGGER AUTOINC_CLI FOR maTable
BEFORE INSERT AS
BEGIN 
   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 :

 
Sélectionnez

BEGIN TRANSACTION
DECLARE NEW_ID INTEGER
SET NEW_ID = GEN_ID(monGenerateur, 1)
INSERT INTO MaTable (ID, VALEUR) VALUES (NEW_ID, 'xxx')
...
COMMIT TRANSATION 

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 :

 
Sélectionnez

-- Création d'une table avec colonne auto incrémentée :
CREATE TABLE maTable
(LaClef     INTEGER IDENTITY(6852,1) NOT NULL PRIMARY KEY,
 UneColonne VARCHAR(32))

-- Récupération de la dernière valeur insérée :
INSERT INTO maTable (UneColonne) VALUES ('exemple')
SELECT @@IDENTITY

---------------------------------------- 
6852

-- Débranchement de l'autoincrément pour insertion manuelle :
SET IDENTITY_INSERT maTable ON
INSERT INTO maTable (LaClef, UneColonne)
VALUES (623, 'exemple 2')
INSERT INTO maTable (LaClef, UneColonne)
VALUES (998877, 'exemple 3')
SET IDENTITY_INSERT maTable OFF

-- Vérification 
INSERT INTO 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 !

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

  

Copyright © 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.