Clefs auto incrémentées

Date de publication : 26/08/2003

Par SQLPro (autres articles) (CV)
 

niveau : intermédiaire

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

1. Qualité d’une bonne clef

• Concision
• Unicité
• Stabilité

2. Clef naturelle ou clef numérique ?

3. L’auto incrément

3.1. Une fausse bonne idée

SELECT MAX(LaColonneClef) + 1
FROM MaTable

• s’assurer que la clef soit TOUJOURS utilisée et non abandonnée
• s’interdire la concurrence des utilisateurs

• 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

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

4. Les mécanismes internes aux SGBDR

CREATE GENERATOR monGenerateur TO 2301;

CREATE TRIGGER AUTOINC_CLI FOR maTable
BEFORE INSERT AS
BEGIN 
   NEW.laClef = GEN_ID(monGenerateur, 1);
END

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

• 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

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

6. A lire sur le sujet