Most tables need a surrogate key: a number that identifies each row and means nothing else. Oracle used to need a sequence and a trigger or a default for that. An identity column generates the number itself, from an internal sequence that belongs to the column, and is the standard way to define keys today.
Code for This Guide
The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
column number generated {always | by default [on null]} as identity [(sequence_options)]| Option | Explicit value in INSERT | Explicit NULL |
|---|---|---|
| GENERATED ALWAYS | Rejected | Rejected |
| GENERATED BY DEFAULT | Accepted | Rejected (the column is NOT NULL) |
| GENERATED BY DEFAULT ON NULL | Accepted | Replaced by a generated value |
Sequence options such as START WITH, INCREMENT BY, and CACHE follow in parentheses.
GENERATED ALWAYS
This table numbers promotion codes from 100 in steps of 10, and refuses an explicit key.
Example:
create table promo_codes (
promo_id number generated always as identity (start with 100 increment by 10),
code varchar2(12) not null,
discount number(3)
);
insert into promo_codes (code, discount) values ('SPRING26', 15), ('FLYDXB', 10);
select * from promo_codes;
insert into promo_codes (promo_id, code, discount) values (1, 'MANUAL', 5);Output:
Table PROMO_CODES created.
2 rows inserted.
PROMO_ID CODE DISCOUNT
___________ ___________ ___________
100 SPRING26 15
110 FLYDXB 10
Error starting at line : 10
In command -
insert into promo_codes (promo_id, code, discount) values (1, 'MANUAL', 5)
Error at Command Line : 10 Column : 26
Error report -
SQL Error: ORA-32795: cannot insert into a generated always identity columnThe two codes get 100 and 110 automatically. Supplying promo_id raises ORA-32795, which protects the key from being set by hand.
GENERATED BY DEFAULT ON NULL
BY DEFAULT ON NULL generates a value when the column is left out or set to NULL, and accepts explicit values, which suits data loads that bring their own keys.
Example:
create table vouchers (
voucher_id number generated by default on null as identity,
code varchar2(10)
);
insert into vouchers (code) values ('A1'); -- generated
insert into vouchers values (null, 'B2'); -- NULL: generated too
insert into vouchers values (50, 'C3'); -- explicit value accepted
select voucher_id, code from vouchers order by voucher_id;
select column_name, generation_type
from user_tab_identity_cols
where table_name = 'VOUCHERS';Output:
Table VOUCHERS created.
1 row inserted.
1 row inserted.
1 row inserted.
VOUCHER_ID CODE
_____________ _______
1 A1
2 B2
50 C3
COLUMN_NAME GENERATION_TYPE
______________ __________________
VOUCHER_ID BY DEFAULTUSER_TAB_IDENTITY_COLS describes identity columns and their options.
Continue after Loaded Keys
When rows were loaded with explicit keys, the internal sequence does not know about them and will eventually generate a duplicate. START WITH LIMIT VALUE restarts it after the highest value in the table.
Example:
insert into vouchers2 values (1, 'MANUAL'); -- an explicit key, as from a data load
insert into vouchers2 (code) values ('NEXT'); -- the sequence also starts at 1: ORA-00001
alter table vouchers2 modify voucher_id generated by default as identity (start with limit value);
insert into vouchers2 (code) values ('NEXT');
select voucher_id, code from vouchers2 order by voucher_id;Output:
1 row inserted.
Error starting at line : 2
In command -
insert into vouchers2 (code) values ('NEXT')
Error report -
ORA-00001: unique constraint (NIMBUS.SYS_C0018064) violated on table
NIMBUS.VOUCHERS2 columns (VOUCHER_ID)
ORA-03301: (ORA-00001 details) row with column values (VOUCHER_ID:1) already
exists
Table VOUCHERS2 altered.
1 row inserted.
VOUCHER_ID CODE
_____________ _________
1 MANUAL
2 NEXTThe first generated key collided with the loaded key 1. After the restart, the next one is 2.
Things to Know
- An identity column is NOT NULL and, by itself, not unique: declare it PRIMARY KEY.
- RETURNING INTO gets the generated key back from an INSERT in PL/SQL and most drivers.
- Each table can have one identity column.
Related Guides
Conclusion
Identity columns generate surrogate keys from their own sequence. Use GENERATED ALWAYS to forbid manual keys, BY DEFAULT ON NULL to allow them and fill in NULLs, declare the column as the primary key, and restart with START WITH LIMIT VALUE after loading explicit keys.
