A l'instar de SQL Server et MySQL, Oracle offre maintenant le support des colonnes auto incrémentées.
Oracle les gère en interne par des séquences générées par la système.
On ne peut avoir d'une seule colonne IDENTITY par table.
CREATE TABLE table_name (
column_name NUMBER GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY
[ ( sequence_options ) ]
...
);
Par défaut, une colonne est "GENERATED ALWAYS AS IDENTITY", c'est-à-dire que c'est toujours le système qui génère la colonne.
Si l'on souhaite pouvoir modifier la colonne, il faut déclarer la colonne "GENERATED BY DEFAULT AS IDENTITY".
Mais si en plus on ne souhaite jamais voir de valeurs à NULL, alors il faut préciser "GENERATED BY DEFAULT ON NULL AS IDENTITY".
--
-- Creation de la table et de la colonne
--
create TABLE TEST_IDENTITY (
id NUMBER GENERATED AS IDENTITY,
comments VARCHAR2(1000)
);
--
-- Presence d'une sequence dans DBA_OBJECTS et DBA_TAB_COLUMNS
--
col object_name for a40
select object_type, object_name
from user_objects
order by 1,2;
OBJECT_TYPE OBJECT_NAME
----------------------- ----------------------------------------
SEQUENCE ISEQ$$_20589
TABLE TEST_IDENTITY
col table_name for a40
col column_name for a40
select table_name, column_id, column_name, identity_column
from user_tab_columns
order by table_name, column_id;
TABLE_NAME COLUMN_ID COLUMN_NAME IDENTITY_COLUMN
---------------------------------------- ---------- ---------------------------------------- ---------------
TEST_IDENTITY 1 ID YES
TEST_IDENTITY 2 COMMENTS NO
--
-- La séquence ne peut être droppée
--
drop sequence "ISEQ$$_20589";
ORA-32794: impossible de supprimer une séquence générée par le système
--
-- La colonne ne peut pas être forcée par défaut
--
insert into TEST_IDENTITY (comments) values ('A');
1 lignes inséré.
col comments for a40
select * from TEST_IDENTITY;
ID COMMENTS
---------- ----------------------------------------
1 A
insert into TEST_IDENTITY (id, comments) values (2, 'B');
ORA-32795: impossible d'insérer la valeur dans une colonne d'identité avec les mots-clés GENERATED ALWAYS
--
-- Le passage à "GENERATED BY DEFAULT AS IDENTITY" peut générer des doublons (ou erreurs de PK si PK existe)
--
alter table TEST_IDENTITY modify (id number generated by default on null as identity);
table TEST_IDENTITY modifié(e).
insert into TEST_IDENTITY (id, comments) values (2, 'B');
1 lignes inséré.
insert into TEST_IDENTITY (id, comments) values (null, 'C');
1 lignes inséré.
commit;
select * from TEST_IDENTITY;
ID COMMENTS
---------- ----------------------------------------
1 A
2 B
2 C