In my job I work primarily in the Microsoft Stack, but I have to switch DBMS back ends frequently. Unfortunately I find it hard to keep up with changes sometimes and important updates pass me by. This is one that has been available for a few years now, but no one on my mostly SQL Server focused team knew about.
Prior to Oracle 12c there was no built in support for AutoNumber or Identity columns as featured in several other RDMBS products. Instead the pattern was to create a Sequence/Trigger pair and rely on the pair to manage the values in the column.
With the 12c release, Oracle now supports two options for similar behavior. Tables can be defined with a column as
NUMBER GENERATED ALWAYS AS IDENTITY to automatically generate a system linked sequence. Alternatively a table can be defined with a column with
DEFAULT ON NULL [sequence].NEXTVAL.
CREATE TABLE default_test ("ID" NUMBER GENERATED ALWAYS AS IDENTITY, "DESCRIPTION" VARCHAR2(50 BYTE) NOT NULL ENABLE );
Results in a table created with this SQL generated by SQL Developer:
CREATE TABLE "[owner]"."DEFAULT_TEST" ("ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(50 BYTE) NOT NULL ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "[owner]" NO INMEMORY ;
Viewing the columns displays
Note how rather than having the traditional trigger/sequence pair, Oracle created a system generated sequence for you. Then it sets the default value for that row to the
nextval of the system generated sequence.
If you want to delete the table, in this case the sequence is dropped at the same time.
DROP TABLE default_test PURGE;
Removes both the table and the automatically generated sequence (and
PURGE removes them from the recycle area and makes them unavailable for flashback - careful).
You can see the list of identity columns attached to tables by executing:
SELECT table_name, column_name, sequence_name, generation_type, identity_options FROM all_tab_identity_cols WHERE owner = '[owner]' ORDER BY 1, 2;
|APP_CONFIG||ID||ISEQ$$_84588||ALWAYS||START WITH: 309, INCREMENT BY: 1, MAX…|
|APP_CONFIG_TYPES||ID||ISEQ$$_84590||ALWAYS||START WITH: 3, INCREMENT BY: 1, MAX…|
SQLDeveloper Version 17.3.x does not appear to support creation of IDENTITY columns in the table creation dialog. Users have to edit the table creation script manually to create an Identity column.
Behavior similar to the IDENTITY usage above can be achieved using an explicit sequence and using the Oracle 12c enhanced
CREATE SEQUENCE seq_default2_test_id; CREATE TABLE default_test2 ("ID" NUMBER DEFAULT ON NULL SEQ_DEFAULT2_TEST_ID.NEXTVAL, "DESCRIPTION" VARCHAR2(50 BYTE) NOT NULL ENABLE );
Will create a sequence, a table, and will cause the ID field to behave like an AutoNumber or Identity field.
The SQLDeveloper IDE generated SQL for this table is:
CREATE TABLE "[owner]"."DEFAULT_TEST2" ( "ID" NUMBER DEFAULT ON NULL "[owner]"."SEQ_DEFAULT2_TEST_ID"."NEXTVAL" NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(50 BYTE) NOT NULL ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "[owner]" NO INMEMORY ;
Note that in neither case is an explicit primary key constraint generated by default.
If the sequence is linked using
DEFAULT alone rather than
DEFAULT ON NULL there are two significant differences:
- The field is not automatically designated as
- An insert that explicitly passes
NULLto the ID column (as often occurs with
INSERTstatements generated by ORMs) will succeed and will likely cause referential integrity errors or downstream data integrity problem.
Viewing the table columns in SQLDeveloper displays:
In this case the deletion of the table will not delete the sequence automatically. It is also possible to drop the sequence without dropping the table, which due to the dependency on the sequence will cause a runtime error if the insert does not include a value for the ID column.
SQL Error: ORA-02289: sequence does not exist
To drop both objects execute:
DROP SEQUENCE seq_default2_test_id; DROP TABLE default_test2 PURGE;
Documentation in the resources listed below states
Not surprisingly, [a trigger/sequence-based table] performs much worse than the others. The direct use of a sequence [using
DEFAULT] and the 12c identity column give comparable results, which are typically an order of magnitude faster than using a trigger to populate the ID column. link
If you are using small tables in a situation where you do very infrequent inserts, then sticking with a trigger/sequence pair is fine and will be easily understood by developers who have worked with older Oracle versions.
If however you have any concerns about scale and load, I would recommend switching to one of the two newer techniques.
"ID" NUMBER GENERATED ALWAYS AS IDENTITY if:
- You have no need to access the sequence directly (see related bullet for
- You want to ignore the creation of the sequence and let the DB handle that automatically.
- You don’t mind your schema being cluttered with sequences named like ISEQ$$_219376
- You want the DB to automatically handle removing the sequence if you remove the table.
"ID" NUMBER DEFAULT ON NULL [sequence_name].nextval if:
- You need access to the returned sequence value as part of a multi-step process or transaction in an API, business layer or ORM and are going to insert it manually. In this case you use a sequence that meets your organizations naming convention.
- You have a strict naming convention for sequences and want to avoid the auto-generated sequence name.
- You need to ensure that sequence names are identical between Dev, Test and Production schemas.
- You are ok with managing the deletion or modification of both objects if one of them is dropped.