Méthode classique
PostgreSQL et Oracle Database ne disposent pas dune propriété "AUTOINCREMENT" à appliquer sur une colonne dans le but de définir une clé primaire technique. Il a cependant toujours été possible de créer une clé primaire auto-incrémentée avec ces SGBD gràce à un objet standard SQL, la séquence. PostgreSQL accordait une grande facilité décriture par rapport à Oracle Database en ne nécessitant pas de trigger et en permettant de créer implicitement la séquence gràce aux types SERIAL.
Avant de présenter les nouveautés Oracle 12c / PostgreSQL 10, rappel avec Oracle Database XE 11.2.0.2 et PostgreSQL 9.6 (autocommit off) :
Que peut-on conclure de ces quelques commandes ? Le trigger créé sous Oracle remplace la valeur ou plutôt labsence de valeur par défaut, NULL, par la valeur suivante de la séquence s1 pour la colonne c1 lors dune insertion dans la table t1. Il est donc possible domettre la colonne ou décrire NULL ou DEFAULT dans la clause VALUES si la colonne c1 nest pas omise.
Autre méthode
PostgreSQL se comporte de manière similaire, à lexception de limpossibilité dinsérer NULL au niveau de c1 en espérant obtenir lauto-incrément souhaité. Cela déclenche une erreur, conformémement dailleurs à la contrainte NOT NULL implicitement définie par le fait que c1 est la clé primaire. Je déconseille donc cette écriture avec Oracle Database puisquil existe une syntaxe commune avec PostgreSQL moins douteuse. De plus, vous verrez dans la suite de larticle que cette écriture ne fonctionnera pas avec la nouvelle méthode proposée.
Les mécanismes utilisés dans les exemples autorisent les insertions explicites de valeurs au niveau de la colonne c1 plutôt que de compter sur lauto-incrément. Cependant, mélanger auto-incrément et insertion explicite de valeurs pour une colonne est à éviter absolument. Cela déclenchera en effet une erreur si la valeur entrée est plus tard atteinte par la séquence lors dune insertion.
Enfin, il faut remarquer que nous navons détruit aucune ligne dans les exemples. Mais nous avons malgré tout des trous dans la suite de valeurs de c1 dans la table t1. Utiliser une séquence non cyclique garantit en effet seulement lunicité.
Le standard SQL:2003 définit la propriété IDENTITY pour les colonnes, permettant également dobtenir des clés primaires auto-incrémentées. Oracle a adopté cette fonctionnalité en 12.1 et PostgreSQL avec la version 10.
Démonstration avec Oracle Database EE 12.2.0.1 et PostgreSQL 10 devel (autocommit off) :
Conclusion
Il est à présent possible de créer simplement des clés primaires auto-incrémentées avec une syntaxe commune à Oracle et PostgreSQL par lintermédiaire des colonnes GENERATED AS IDENTITY.
PostgreSQL étend de plus les fonctionnalités liées aux colonnes GENERATED AS IDENTITY au niveau de la commande TRUNCATE, conformément au standard SQL.
Mise à jour : 14/04/2017