How to Create Auto-Increment Field in Oracle.

Suppose we would like value of primary key  field to be created automatically every time a new record is inserted.
Auto-increment allows a unique number to be generated when a new record is inserted into a table.
MySql has a keyword "auto_increment" and SqlServer has a keyword "Identity" that will create unique identifiers for that column.

In Oracle its a bit tricky because you dont have a keyword to perform this task.

One way to do is by creating two database objects, a sequence and a trigger.

Suppose we have a table called "example" with two columns "id" and "name"

create table example (id number, name varchar2(255)); 

Now,we'll create a sequence name example_seq for id column

create sequence example_seq 
start with 1 
increment by 1 
nomaxvalue; 

It creates a sequence starting with 1 and will increment by 1.
The "nomaxvalue" tells it to keep incrementing forever as opposed to resetting at some point.

Next, we'll create the trigger that will automatically insert the next number from the sequence into the id column.

create trigger example_trigger
before insert on example
for each row
begin
select example_seq.nextval into :new.id from dual;
end;

Every time a new row is inserted into example, the trigger will get the next number in the sequence from example_seq and set the "id" column for that row to whatever the sequence number is.


We can also use the sequence directly in the "Insert"  statement instead of trigger.

insert into example values(example_seq.nextval, 'john');

This code would insert a new record in example table.ID column would be  assigned next value from example_seq and name is set to john..