Auto Increment for Oracle

Question

I need to create a sequence and a trigger to auto-increment the primary key on a table but I have no idea on how to do it.

Answer 1

Create the table and the sequence

SQL> create table staff (   2    emp_id number primary key,   3    staff_name varchar2(100)   4  );  Table created.  SQL> create sequence emp_id_seq;  Sequence created. 

Now, you can create a trigger that uses the sequence to populate the primary key

SQL> create trigger trg_emp_id   2    before insert on staff   3    for each row   4  begin   5    select emp_id_seq.nextval   6      into :new.emp_id   7      from dual;   8  end;   9  /  Trigger created. 

Now, when you insert data, you woon't need to specify the EMP_ID column-- it will automatically be populated by the trigger

SQL> insert into staff( staff_name ) values ('Justin');  1 row created.  SQL> select * from staff;      EMP_ID STAFF_NAME ---------- --------------------          1 Justin 

Answer 2

Read this, Beautiful article.

how sequence [auto increment in oracle]

syntax

Create sequence sequence_name start with value increment by value minvalue value maxvalue value; 

example

SQL> create table emp ( emp_id number(10), fname varchar2(25), lname varchar2(25), constraint pk_emp_id PRIMARY KEY(emp_id) );  SQL> Create sequence emp_sequence start with 1 increment by 1 minvalue 1 maxvalue 10000;  SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Darvin','Johnson'); SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Mig','Andrews'); SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Alex','Martin'); SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Jon','paul'); SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Yatin','Bones'); 

in emp_sequence.nextval where emp_sequence is the name of sequence we created above and nextval is a function that is used to assign the next number from emp_sequence to emp_id column in emp table.

SQL> select * from emp;    EMP_ID FNAME                     LNAME ---------- ------------------------- -------------------------          1 Darvin                    Johnson          2 Mig                       Andrews          3 Alex                      Martin          4 Jon                       paul          5 Yatin                     Bones