Auto Increment Column – Sequence as Default Value in Oracle

Auto Increment Column is a quite common requirement in the coding. Here are some of the ways to get that in Oracle

Auto increment in oracle

This feature( Oracle Auto Increment Column – Sequence as Default Value) is not present in the Oracle database so far. But with 12c, oracle is able to provide that.

Suppose we want to set an oracle table where the sequence should be auto-incremented on insert . There are many ways to achieve it

Option 1
Prior to Oracle 12c, we need to use before insert triggers to achieve it

create table test_lab
(
id number primary key
name varchar (10)
);

create sequence test_lab_seq start with 1 increment by 1 nocycle;

create or replace trigger test_lab_bef_auto
before insert on test_lab
for each row
begin
select test_lab_seq.nextval into :new.id
from dual;
end;
/

or

create or replace trigger test_lab_bef_auto
before insert on test_lab
for each row
begin
:new.id := test_lab_seq.nextval;
end;
/

We can specify when the id is null in triggers to avoid the errors when the insert statement is having a value of the id column

insert into test_lab values ('john');
insert into test_lab values ('scott');
insert into test_lab values ('bill');
insert into test_lab values ('tom');
insert into test_lab values ('Duke');
commit;
select * from test_lab;

Option 2:
With Oracle 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of the sequence, you just need to declare it with the table definition.

create sequence test_lab_seq start with 1 increment by 1 nocycle;

create table test_lab
(
id number default test_lab_seq.nextval primary key
);

insert into test_lab values ('Cat1');
insert into test_lab values ('Cat2');
insert into test_lab values ('Cat3');
insert into test_lab values ('Cat4');
insert into test_lab values ('Cat5');
commit;
select * from test_lab;

Option 3
With 12c, we can use the identify feature

create table test_lab (
id number(10) GENERATED AS IDENTITY,
name varchar(15)
);
Table created.

Desc TEST_LAB
Name Null? Type
---- ----   ----
ID NOT NULL NUMBER(10)
NAME VARCHAR2(15)

• We can see that the NOT NULL constraint is automatically imposed on the Identity column
• Oracle internally uses the sequence to populate the auto-increment column values.

insert into test_lab values ('London');
insert into test_lab values ('New york');
insert into test_lab values ('Tokyo');
insert into test_lab values ('Delhi');
insert into test_lab values ('Bangalore');
commit;
select * from test_lab;

Related Oracle Sql Articles

how to create table in oracle
How to create Oracle views
Top-N Queries and Pagination
how to write sql queries
Oracle sql decode processing