Discussion :

[Hemgé]

Bonjour,

Pour bien cadrer mes notions dès le début :

Sachant qu’une clé primaire n’admet ni doublon ni valeur nulle et en se limitant à la création de la clé primaire dans la définition de la colonne, si on veut créer une clé primaire de type numérique, auto-incrémentée, la documentation (9.3) nous indique plusieurs voies:

Tout d’abord (II.8.1.4, types sériés) :

1.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE nom_de_table (
nom_de_colonne SERIAL
);

qui est équivalent à écrire :

2.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE SEQUENCE nom_de_table_nom_de_colonne_seq;
CREATE TABLE nom_de_table (
nom_de_colonne integer NOT NULL DEFAULT 
extval('nom_de_table_nom_de_colonne_seq')
NOT NULL
);
ALTER SEQUENCE nom_de_table_nom_de_colonne_seq OWNED BY nom_de_table.nom_de_colonne;

On comprend que suivant les besoins, nom_de_colonne pourra être de type integer ou bigint, ce qui correspondra respectivement à serial et bigserial.

Pour obtenir la clé primaire dans les deux exemples ci-dessus, il faut encore ajouter la contrainte UNIQUE à la colonne nom_de_colonne.
On pourrait remplacer les contraintes NOT NULL UNIQUE par PRIMARY KEY (avec une redondance sur NOT NULL dans le cas de l’utilisation de serial, puisque la séquence développée comprend déjà cette contrainte).
Outre d’être équivalente à la combinaison ‘NOT NULL UNIQUE’, PRIMARY KEY a une valeur sémantique propre indiquant un identifiant unique de ligne.

Ensuite, dans VI. Commandes SQL, CREATE TABLE, on trouve les exemples suivants :

3.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE distributeurs (
did integer PRIMARY KEY DEFAULT nextval('serial'),
…

4.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE distributeurs (
did integer PRIMARY KEY,
…

Est-ce que je raisonne correctement si j’analyse tout cela comme suit :

L’exemple 4 ne pourrait fonctionner qu’avec une valeur ‘did’ fournie par le programme et n’aboutirait donc pas un identifiant automatique.

L’exemple 2 est la seule méthode a priori compatible avec tous les SGBD dans le cadre de la norme SQL.

Il n’y a pas de différence entre l’exemple 1 (complété par PRIMARY KEY) et l’exemple 3 et, dans l’environnement Postgresql, je préférerais alors la formulation de l’exemple 1 complétée comme suit pour créer une clé primaire numérique auto-incrémentée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE nom_de_table (
nom_de_colonne SERIAL PRIMARY KEY 
);

Je vous remercie déjà de vos commentaires et lumières,

Hemgé

[punkoff]

bonjour,

Quelle est la question en fait ?


Sinon il vous manque l'approche par trigger (before insert) + sequence

[Hemgé]

Bonjour,

La question était

Est-ce que je raisonne correctement si j’analyse tout cela comme suit :
...

Avec in fine la confirmation du choix d'une méthode :

Si on privilégie la portabilité : code de l'exemple 2.

Si on travaille dans un environnement PostgreSQL sans nécessité de prévoir la portabilité : le code retenu en fin de message.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE nom_de_table (
nom_de_colonne SERIAL PRIMARY KEY 
);

Au passage, y a-t-il une différence qui m'aurait échappé entre ce code et celui de l'exemple 3 ?

Merci

Hemgé

[punkoff]

Le code de l'exemple 2 ne marchera pas de partout je pense.

Par exemple sous oracle il faut créer un trigger afin d'assigner automatiquement un nombre avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select MA_SEQ.nextval into :new.ID from dual;

Pour le cas postgres, soit vous utilisez

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
nom_de_colonne SERIAL PRIMARY KEY

Soit vous passez par un trigger si vous voulez forcer la valeur sans que l'utilisateur puisse mettre ce qu'il veut dans la PK.

[SQLpro]

Une clef primaire et une contrainte d'unicité sont deux choses différentes.
Il ne peut y avoir qu'une seule clef primaire et cela constitue la clef de la relation dans le modèle conceptuel ou logique. Elle suppose en outre la non nullité des colonnes composant la clef.
La contrainte d'unicité représente les clefs "alternatives" ou "subrogées".

Aucune de vos requêtes n'est compatible avec la norme SQL. En effet SERIAL comme BIGSERIAL n'existent pas. ALTER SEQUENCE existe bien, mais pas avec "OWNED BY" (spépcifique PG). Enfin obtenir la valeur suivante d'un séquenceur se fait dans la norme SQL:2003 par NEXT VALUE FOR nom_sequence.

A +

[Hemgé]

A punkoff
Merci,

Pourriez-vous me formuler une solution avec un trigger, que je puisse étudier la "bête" ?

Qu'entendez-vous par "forcer la valeur sans que l'utilisateur puisse mettre ce qu'il veut dans la PK."?
Je suppose qu'avec la solution via le "serial", l'utilisateur ne peut pas choisir sa valeur.
Et cela signifierait que je pourrais, le cas échéant, choisir mes valeurs en recourant au trigger ?
Simple curiosité intellectuelle, mais si je devais cesser d'être curieux ...

A SQLpro
Merci également, mais j'aimerais bien que vous alliez jusqu'au bout de la leçon et répondiez à mon souci didactique.
Si vous me mettiez un code propre et compatible 100% pour illustrer vos propos, j'en serais ravi et là aussi, je pourrais étudier la "bête".

A vous lire l'un et l'autre en vous remerciant encore et déjà pour la suite

Hemgé

[SQLpro]

Il n'existe aucun code SQL compatible à 100% pour les principaux SGBDR.
En effet :

• Oracle à inventé les séquences et les a formalisées à sa sauce.
• SQL Server a inventé l'IDENTITY et l'a formalisé à sa sauce
• PostGreSQL a inventé le SERIAL et l'a formalisé à sa sauce.
• La norme SQL a retenu en 2008 IDENTITY et SEQUENCE avec un formalisme différent de celui d'Oracle ou de SQL Server. Pas le SERIAL.
• SQL Server a repris le SEQUENCE version normative mais incomplet dans la version 2012.

A +

[ericd69]

salut,

pour compléter, une clé primaire c'est:

• un index automatique sur la colonne
• une contrainte d'unicité
• une contrainte de non nullité
• un identifiant unique de ligne

la plupart des sgbdr en généreront une automatiquement si tu ne la précise pas, selon des règles qui leur sont propres et généralement en voyant très large (donc pas optimisé du tout).

à noter que tout autre index est construit en se référant à ses valeurs, d'où l'intérêt d'utiliser une valeur numérique pour une raison évidente de place et d'efficacité de manipulation.

De même, certaines fonctions comme count sont optimisées selon les sgbdr pour compter le nombre de valeur dedans.
Elle est consultée dans la plupart des opérations de lecture ou écriture. D'où l'intérêt de bien le dimensionner au plus juste et si possible avec un type numérique.

Le trigger est une façon de ne pas utiliser l'insert, update, delete ou truncate "normal" pour généralement avoir un comportement plus complexe. admettons que tu veuilles générer une clé primaire alphanumérique automatiquement alors aucune séquence ne le permettrait (sous postgreSQL). un "trigger before insert" pourrait le faire. Mais ce genre de clé est plutôt à proscrire vu ce qui a été dit avant même si elle est tout à fait possible techniquement.