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
.
Tables Using IDENTITY For Surrogate Keys
Creation
The command:
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
COLUMN_NAME | DATA_TYPE | NULLABLE | DATA_DEFAULT |
---|---|---|---|
ID | NUMBER | NO | “[owner]”.”ISEQ$$_246189”.nextval |
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.
Deletion
If you want to delete the table, in this case the sequence is dropped at the same time.
Executing:
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).
Additional Details
Viewing Identity Column details
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;
Sample result
TABLE_NAME | COLUMN_NAME | SEQUENCE_NAME | GENERATION | IDENTITY_OPTIONS |
---|---|---|---|---|
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… |
IDE Support
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.
Tables Using DEFAULT ON NULL For Surrogate Key
Behavior similar to the IDENTITY usage above can be achieved using an explicit sequence and using the Oracle 12c enhanced DEFAULT
keyword.
Creation using DEFAULT ON NULL
Executing:
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.
DEFAULT vs. DEFAULT ON NULL
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
NOT NULL
- An insert that explicitly passes
NULL
to the ID column (as often occurs withINSERT
statements generated by ORMs) will succeed and will likely cause referential integrity errors or downstream data integrity problem.
Viewing the table columns in SQLDeveloper displays:
COLUMN_NAME | DATA_TYPE | NULLABLE | DATA_DEFAULT |
---|---|---|---|
ID | NUMBER | NO | “[owner]”.”SEQ_DEFAULT2_TEST_ID”.”NEXTVAL” |
Deleting Default Sequences
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;
Why Switch?
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.
How To Choose Which Technique To Use
Use "ID" NUMBER GENERATED ALWAYS AS IDENTITY
if:
- You have no need to access the sequence directly (see related bullet for
DEFAULT
below) - 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.
Use "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.
Top comments (0)