Clés primaires auto-incrémentées

Méthode classique

      PostgreSQL et Oracle Database ne disposent pas d’une 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) :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production create sequence s1; Sequence created. create table t1(c1 integer primary key, c2 character varying(10)); Table created. CREATE OR REPLACE TRIGGER autot1c1 BEFORE INSERT ON t1 FOR EACH ROW WHEN (new.c1 IS NULL) BEGIN SELECT s1.NEXTVAL INTO :new.c1 FROM dual; END; / Trigger created. insert into t1(c1,c2) values(null,'A'); 1 row created. commit; Commit complete. insert into t1(c1,c2) values(2,'B'); 1 row created. commit; Commit complete. insert into t1(c2) values('C'); insert into t1(c2) values('C') * ERROR at line 1: ORA-00001: unique constraint (SYSTEM.SYS_C007279) violated insert into t1(c2) values('C'); 1 row created. select * from t1; C1 C2 ---------- ---------- 1 A 2 B 3 C insert into t1(c2) values('D'); 1 row created. rollback; Rollback complete. insert into t1(c2) values('E'); 1 row created. insert into t1 values(default, 'F'); 1 row created. select * from t1; C1 C2 ---------- ---------- 1 A 2 B 5 E 6 F select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-10) 6.3.0 20170321, 64-bit (1 ligne) create table t1(c1 serial primary key, c2 character varying(10)); CREATE TABLE insert into t1(c1,c2) values(null,'A'); ERREUR: une valeur NULL viole la contrainte NOT NULL de la colonne " c1 " DÉTAIL : La ligne en échec contient (null, A) insert into t1(c1,c2) values(2,'B'); INSERT 0 1 commit; COMMIT insert into t1(c2) values('C'); INSERT 0 1 insert into t1(c2) values('C'); ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique " t1_pkey " DÉTAIL : La clé " (c1)=(2) " existe déjà. select * from t1; c1 | c2 ----+---- 2 | B 1 | C (2 lignes) insert into t1(c2) values('D'); INSERT 0 1 rollback; ROLLBACK insert into t1(c2) values('E'); INSERT 0 1 insert into t1 values(default, 'F'); INSERT 0 1 select * from t1; c1 | c2 ----+---- 2 | B 4 | E 5 | F (3 lignes)

      Que peut-on conclure de ces quelques commandes ? Le trigger créé sous Oracle remplace la valeur ou plutôt l’absence de valeur par défaut, NULL, par la valeur suivante de la séquence s1 pour la colonne c1 lors d’une insertion dans la table t1. Il est donc possible d’omettre la colonne ou d’écrire NULL ou DEFAULT dans la clause VALUES si la colonne c1 n’est pas omise.
      PostgreSQL se comporte de manière similaire, à l’exception de l’impossibilité d’insérer NULL au niveau de c1 en espérant obtenir l’auto-incrément souhaité. Cela déclenche une erreur, conformémement d’ailleurs à 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 puisqu’il existe une syntaxe commune avec PostgreSQL moins douteuse. De plus, vous verrez dans la suite de l’article 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 l’auto-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 d’une insertion.
      Enfin, il faut remarquer que nous n’avons 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 l’unicité.

Autre méthode

      Le standard SQL:2003 définit la propriété IDENTITY pour les colonnes, permettant également d’obtenir 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) :

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production create table t1(c1 integer generated always as identity primary key, c2 character varying(10)); Table created. insert into t1(c1,c2) values(null,'A'); insert into t1(c1,c2) values(null,'A') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column insert into t1(c1,c2) values(2,'B'); insert into t1(c1,c2) values(2,'B') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column insert into t1(c2) values('C'); 1 row created. select * from t1; C1 C2 ---------- ---------- 1 C commit; Commit complete. insert into t1(c2) values('D'); 1 row created. rollback; Rollback complete. insert into t1(c2) values('E'); 1 row created. insert into t1 values(default, 'F'); 1 row created. select * from t1; C1 C2 ---------- ---------- 1 C 3 E 4 F truncate table t1 restart identity; truncate table t1 restart identity * ERROR at line 1: ORA-03291: Invalid truncate option - missing STORAGE keyword select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-12) 6.3.0 20170406, 64-bit (1 ligne) create table t1(c1 integer generated always as identity primary key, c2 character varying(10)); CREATE TABLE insert into t1(c1,c2) values(null,'A'); ERREUR: cannot insert into column "c1" DÉTAIL : Column "c1" is an identity column defined as GENERATED ALWAYS. ASTUCE : Use OVERRIDING SYSTEM VALUE to override. insert into t1(c1,c2) values(2,'B'); ERREUR: cannot insert into column "c1" DÉTAIL : Column "c1" is an identity column defined as GENERATED ALWAYS. ASTUCE : Use OVERRIDING SYSTEM VALUE to override. insert into t1(c2) values('C'); INSERT 0 1 select * from t1; c1 | c2 ----+---- 1 | C (1 ligne) commit; COMMIT insert into t1(c2) values('D'); INSERT 0 1 rollback; ROLLBACK insert into t1(c2) values('E'); INSERT 0 1 insert into t1 values(default, 'F'); INSERT 0 1 select * from t1; c1 | c2 ----+---- 1 | C 3 | E 4 | F (3 lignes) truncate table t1 restart identity; TRUNCATE TABLE insert into t1(c2) values('C'); INSERT 0 1 select * from t1; c1 | c2 ----+---- 1 | C (1 ligne)

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 l’intermé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