Oracle Auto Increment Column - Sequence as Default Value

 

 

Solution 1: Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only in trigger, which requires context switching from PL/SQL engine to SQL engine. So we need to create before insert trigger for each row, and assign sequence new value to the column using select into clause.

create table test_tab
(
    id number primary key
);

create sequence test_seq start with 1 increment by 1 nocycle;

create or replace trigger test_trg 
before insert on test_tab 
for each row 
begin
    select test_seq.nextval into :new.id
    from dual;
end;
/

Solution 2: From Oracle 11g, we can directly assign a sequence value to a pl/sql variable in trigger, So we can create before insert trigger for each row, and assign sequence nextval to the column directly.

create table test_tab
(
    id number primary key
);

create sequence test_seq start with 1 increment by 1 nocycle;

create or replace trigger test_trg 
before insert on test_tab 
for each row 
begin
    :new.id := test_seq.nextval;
end;
/

Solution 3: 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 sequence, you just need to declare it with table definition.

create sequence test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);