[IB6][BCB5]Constituer une clé primaire !!?? [FAQ] [Résolu]

[Sitting Bull]

Bonjour à tous,

Comment créer une clé primaire pour une table lorsque les seules valeurs uniques de cette table sont un ensemble de chaînes qui dépasse largement la taille que peut supporter ma clé primaire. Est il possible de faire une fonction qui me permettrait de calculer un entier unique en fonction d'un ensemble de chaîne ?

Merci d'avance.
@+

[Barbibulle]

Il y a deux grandes écoles pour la définition des cles primaires.

A-Utiliser une ou plusieures colonnes de la table pour créer la clé primaire
B-Créer une clé primaire entièrement artificielle (un integer en général) que l'on incrémente avec un générateur.

Bien entendu il n'est pas interdit dans une base de données d'utiliser la méthode A pour certaine tables et la B pour d'autres...

En fait chaque méthodes a ses avantages et inconvéniants.

La A :
+ La clé veux dire quelque chose (Elle a un sens et plus compréhensible (représentatif))
+ Les tris suivant cette clé primaires ont un sens.
- Taille de la clé primaire souvant très grande et donc l'index qui gère la clé est plus important et les clé étrangères faisant référence à cette clé sont grosses également.
- Souvant plus difficile à gérer et à générer (unicité).

La B :
+ Gestion d'unicité 100% fiable grace aux générateurs
+ Petite taille de la clé (donc index plus petits et références externes (foreign key) également)
+ On connait l'ordre d'insertion des éléments de la table (ca peut etre interressant de le connaitre)
- La clé ne veux rien dire fonctionnenent, ce n'est qu'une référence interne
- Le tri sur la clé primaire est le plus souvant inutile car le plus souvant de peu d'intéret (mis à par pour connaitre l'ordre de création des éléments)

Pour ma part j'utilise plutot la B et j'ajoute un indexe unique sur les colonnes qui m'aurait servi de clé primaire pour la méthode 1.

[Sitting Bull]

D'accord je conçois que la methode B me semble effectivement beaucoup plus pratique cependant je retrouve avec un probleme sur l'une de mes tables construite de la maniere B (cle primaire = champ auto).

Tout d'abord j'utilise un TIBDataSet, et lorsque que j'insere un enregistrement dans ma table, je souhaite aussitot apres connaitre la valeur du nouveau champ auto afin d'effectuer mes liens avec les tables esclaves, et la je me retrouve avec la valeur 1 pour le champ auto qui est la valeur que je lui est transmit et non la valeur generer par le generateur.

Evidement dans ma table le champ auto est correct.

Le probleme ne pourrait t-il pas venir de la requete RefreshSQL du TIBDataSet ->

Code :

 SELECT A0, A1 FROM MATABLE WHERE AUTO_ID = :OLD_AUTO_ID

Effectivement il me semblerait logique que le parametre OLD_AUTO_ID soit egal à 1 et non à la nouvelle valeur du champ auto, et donc mon Refresh ne peut fonctionner correctement.


En tout cas merci pour cette réponse complete, et merci pour votre travail.
@+

[Barbibulle]

Lorsqu'on a besoin de connaitre la valeur généré il ne faut pas laisser le trigger le générer mais demander la nouvelle valeur avant insertion.
Sinon autre technique créer une procédure stockée qui se charge de l'insertion puis de renvoyer la valeur de l'identifiant.

Solution 1 : (La plus courante)
Par exemple :

Code :

1
2
3
4
5
6
7
8
CREATE TABLE CANDIDATS_IB (
    ID                 INTEGER NOT NULL,
    TITRE              VARCHAR(50) NOT NULL,
    NOM                VARCHAR(35),
    PRENOM             VARCHAR(30));
 
 
ALTER TABLE CANDIDATS_IB ADD PRIMARY KEY (ID);

Puis création du gérérateur :

Code :

CREATE GENERATOR GEN_CANDIDATS_IB_ID;

Enfin du trigger (qui sera utile dans le cas ou on a pas besoin de connaitre la valeur de l'ID)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SET TERM ^ ;
 
CREATE TRIGGER CANDIDATS_IB_BI FOR CANDIDATS_IB
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_CANDIDATS_IB_ID,1);
END
 
^
 
SET TERM ; ^

Donc un simple

Code :

1
2
INSERT INTO CANDIDATS_IB(ID, TITRE, NOM, PRENOM)
VALUES (NULL, 'M', 'Martin', 'Bernard')

ou

Code :

1
2
INSERT INTO CANDIDATS_IB(TITRE, NOM, PRENOM)
VALUES ('M', 'Dupond', 'Alain')

Fonctionneront parfaitement et auront un ID unique.

Maintenant si vous avez besoin de connaitre l'ID voici comment il faut procéder :
Commencer par faire un

Code :

SELECT GEN_ID(GEN_CANDIDATS_IB_ID,1) FROM RDB$Database;

Afin de récupérer la prochaine valeur du générateur puis l'utiliser dans l'insert pour la colonne ID.
sinon vous pouvez créer une PS qui retourne la prochaine valeur

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SET TERM ^ ;
 
CREATE PROCEDURE SP_GEN_CANDIDATS_IB_ID
RETURNS (ID INTEGER)
AS
BEGIN
  ID = GEN_ID(GEN_CANDIDATS_IB_ID, 1);
  SUSPEND;
END
 
^
 
SET TERM ; ^

Il ne vous reste plus qu'a remplacer le select gen_id ... par un appel à cette PS ou par un select:

Code :

SELECT ID FROM SP_GEN_CANDIDATS_IB_ID

Ou autre solution (Solution 2) créer une PS qui insère et retourne la valeur :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SET TERM ^ ;
CREATE PROCEDURE CANDIDATS_IB_I (
    TITRE VARCHAR(50),
    NOM VARCHAR(35),
    PRENOM VARCHAR(30))
RETURNS (
    ID INTEGER)
AS
BEGIN
  ID = GEN_ID(gen_candidats_ib_id,1);
  INSERT INTO CANDIDATS_IB (
    ID,
    TITRE,
    NOM,
    PRENOM)
  VALUES (
    :ID,
    :TITRE,
    :NOM,
    :PRENOM);
  SUSPEND;
END^
SET TERM ; ^

Que l'on utilise soit avec TIBStoredProc ou par un select dans un TIBQuery ou TIBISQL ou TIBDataSet

Code :

SELECT ID form CANDIDATS_IB_I('M','Durand','Pierre');

Voilà les différentes techniques.

[Sitting Bull]

J'aime beaucoup la solution 1, cependant juste une derniere petite question. Je travaille en client-serveur, rien ne me dit que deux utilisateurs ne vont pas lancer la requête de récuperation du générateur en même temps, avant que chacun n'aie pu inserer son enregistrement.
Je risque pas de me retrouver avec des incohérences ?

Merci d'avance.
@+

[Barbibulle]

Non car :
1- Les générateurs sont hors transaction. Ce qui veux dire que quelque soit la transaction vous aurez un numéro unique avec gen_id(Generateur,1)et si vous faites un Rollback apres l'insert celà annule bien l'INSERT mais pas le générateur. (Vous aurez un trou dans votre numérotation, un numéro généré n'est pas annulé donc pas de risque qu'il soit attribué deux fois)
2- Le premier qui demande aura un numéro. Que vous l'utilisiez ou non c'est un autre probleme
Et le second aura le numéro suivant.

[Sitting Bull]

Merci beaucoup pour ces informations.

@+