Tables and Constraints - Pro Oracle Database 12c Administration - page 168

Creating a Table with an Autoincrementing (Identity) Column
Starting with Oracle Database 12c, you can define a column that is automatically populated and incremented when
inserting data. This feature is ideal for automatically populating primary key columns.

■ prior to Oracle Database 12c, you would have to create a sequence manually and then access the sequence
when inserting into the table. sometimes, Dbas would create triggers on tables to simulate an autoincrementing column
based on a sequence (see Chapter 9 for details).

Tip

You define an autoincrementing (identity) column with the GENERATED AS IDENTITY clause. This example
creates a table with primary key column that will be automatically populated and incremented:

create table inv(
inv_id number generated as identity
,inv_desc varchar2(30 char));
--
alter table inv add constraint inv_pk primary key (inv_id);

Now, you can populate the table without having to specify the primary key value:

insert into inv (inv_desc) values ('Book');
insert into inv (inv_desc) values ('Table');

Selecting from the table shows that the INV_ID column has been automatically populated:

select * from inv;

Here is some sample output:

INV_ID INV_DESC
---------- ------------------------------
1 Book
2 Table

When you create an identity column, Oracle automatically creates a sequence and associates the sequence with
the column. You can view the sequence information in USER_SEQUENCES :

SQL> select sequence_name, min_value, increment_by from user_sequences;

Here is some sample output for this example:

SEQUENCE_NAME MIN_VALUE INCREMENT_BY
-------------------- ---------- ------------
ISEQ$$_43216 1 1