Auto increment primary key with Oracle Databases

Suppose you have a database and you want each entry to be identified by a unique number. This can be achieved easily in MySQL by specifying "auto_increment" for the column, but Oracle requires a little bit of extra work to get this done.

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

1. As a simple example, imagine we have a table called "tbl_test" with two columns, "test_id" and "test_data".
create table tbl_test (test_id number, test_data varchar2(145)); 

 2. Now, we create a sequence for the test_id column. Note that the numbering starts with 1 and is incremented by 1.
create sequence seq_test 
start with 1 
increment by 1 
nomaxvalue; 

3. Next we create the trigger that will automatically insert the next number from the sequence into the test_id column.
create trigger tgr_test 
before insert on tbl_test 
for each row 
begin 
select seq_test.nextval into :new.test_id from dual; 
end;
/