Clefs auto-incrémentées

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 clarté est attendue. Que dirait-on d'une enseigne de VPC dont les références des marchandises seraient 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érentes à 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é à 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 commencent à 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, une 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 toutes durant la vie entière de l'objet qu'elle identifie.

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 bien 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 informatiques, 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 à moteur, numéro de sécurité sociale… autant d'informations 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ère 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 automobiles. 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 disparu à 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 !

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ées. 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 où le code réalisant cet auto-incrément est implanté !

3-1. Une fausse bonne idée▲

SELECT MAX(LaColonneClef) + 1
FROM MaTable

3-2. La solution : une table des clefs▲

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

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

UPDATE LesClefs
SET ValeurClefs = ValeurClefs + 1
WHERE NomTable = 'MaTable'

SELECT ValeurClef
FROM LesClefs
WHERE NomTable = 'MaTable'

COMMIT

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

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

Les éditeurs de SGBDR ont proposé diverses solutions pour l'auto incrémentation des tables.
Ainsi des SGBDR comme Paradox ou Access proposent une colonne de type AUTOINC c'est-à-dire un entier dont la valeur est calculée à chaque nouvelle insertion. Bien entendu chaque nouvelle valeur de clef calculée est considérée comme consommée, tant et 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 :

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 :

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 fourni assez tôt un mécanisme d'autogénération de clef incrémentée, 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 :
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

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 à tout prix.

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 !