Simplified Surrogate Keys in Oracle 12c and later

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:

1. The field is not automatically designated as NOT NULL
2. An insert that explicitly passes NULL to the ID column (as often occurs with INSERT 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.

References

• Identity Columns in Oracle Database 12c Release 1 (12.1)
• DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
• Improved Defaults in Oracle Database 12c