Simplified Surrogate Keys in Oracle 12c and later
Tags: sql, database, oracle
Published: 2019-06-14 17:28:03

---
title: Simplified Surrogate Keys in Oracle 12c and later
published: true
tags: sql, database, oracle, 
canonical_url: http://AgileCoder.net/2019/06/05/identity-or-default/
---

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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1#performance)

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)](https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1)
- [DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)](https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1)
- [Improved Defaults in Oracle Database 12c](https://blogs.oracle.com/oraclemagazine/improved-defaults-in-oracle-database-12c)