Identity columns in Oracle 12c

The identity column introduced in Oracle 12c can be used in the different manners, according to your needs. Here are some basic examples:

create table songs (
    song_id number generated always as identity,
    title varchar2(128)
);

The "generated always" clause is default and may be omitted.

In this scenario, no inserts may contain a value for the column song_id, even if the datatype is correct. Neither null values are accepted. Oracle will generate its own value and will not allow any manipulation of the value recorded in the identity column.


Allow for the possibility to override the auto generated numbers by specifying "by default" instead of "always":

create table songs (
    song_id number generated by default as identity,
    title varchar2(128)
);

In other words, the statements below would both be permissible:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;

However, null cannot be specified as a legitimate value for the identity column:

insert into songs values(null, 'White wedding');

would return the error

ORA-01400: cannot insert NULL into ("MUSIC"."SONGS"."SONG_ID")

If you need to allow for NULL values in the identity column, you create the table as follows:

create table songs (
    song_id number generated by default on null as identity,
    title varchar2(128)
);

With the above definition, Oracle

* permits overriding the autogenerated numbers
* allows update of the identity column
* allows null values to be specified during inserts

All of the three statements below are thus valid:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;
insert into songs values(null, 'White wedding');

Tim Hall has published a more in-depth article about the same topic, as usual of the highest quality