Clefs auto-incrémentées

SQLPro

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 »… ♪

- Titre : Clefs auto-incrémentées

- Auteur : SQLPro

- Parution : 26 août 2003

- Licence : 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.

Sommaire

- 1. Qualité d'une bonne clef

- 2. Clef naturelle ou clef numérique ?

- 3. L'auto-incrément

- 3-1. Une fausse bonne idée

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

- 4. Les mécanismes internes aux SGBDR

- 5. CONCLUSION

- 6. À lire sur le sujet

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

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 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 !

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é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

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 :

SELECT MAX(LaColonneClef) + 1
FROM MaTable

Dont la requête SQL renvoie 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…
À un instant donné, la table MaTable contient la clef 48 attribuée à 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. À 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ée à 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 multiutilisateur…
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 relatives à 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ées à 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éressants 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ée, autrement dit : toute clef consommée est perdue ;

- pendant toute la durée qui court 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 à 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 :

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

Elle pourrait contenir les éléments suivants :

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.

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'autocommit…

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ê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ée 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 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

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

6 - À 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 !