Oracle - Using Sequence for Auto Increment

I am working with databases from past few years and I had created or came across hundreds of Tables in database. Out of all those tables 99.99% tables had any of Numeric Primary Key column or Serial Number column or anything similar to this where value of those columns follow numeric incremental pattern.

These are very basic cases of database but there can be so many other cases in database when we need numeric incremental values. For this type of activities Sequence can be very handy.

I am giving a very basic example of How you can use sequence in Oracle? We will perform following activities:
1. Create Table with a Primary Key Field
2. Using sequence while insert into table
3. Achieving Auto Increment using Trigger



Create Table
Creating Table EMPLOYEE with a Primary Key ID

CREATE TABLE EMPLOYEE (
ID NUMBER NOT NULL,
NAME VARCHAR2(100) NOT NULL,
DESIGNATION VARCHAR2(50) NOT NULL,

CONSTRAINT EMPLOYEE_PK PRIMARY KEY(ID)

);

Now insert some rows into this table. See INSERT statements below:

INSERT INTO EMPLOYEE VALUES(1, 'Maecenas','Programmer');
INSERT INTO EMPLOYEE VALUES(2, 'Dolorem','Team Lead');
INSERT INTO EMPLOYEE VALUES(3, 'Consequat','Manager');

Inserting data like this have a problem that before inserting data into table you have to take care that what should be the next value of ID column. So you have to check last value of ID column and then continue with next possible value. Creating sequence can solve this problem.

Using Sequence while insert into table
Creating Sequence SEQ_EMPLOYEE_ID to get incremental value for Primary Key Column ID

CREATE SEQUENCE SEQ_EMPLOYEE_ID
INCREMENT BY 1
START WITH 1;

Now we insert data using the sequence we just created.

Note: Delete all data from table EMPLOYEE as we are starting our sequence from 1 and we already inserted data with value 1 in ID column or you can create your sequence accordingly to start from 4 which should be the next value of ID column. See INSERT statements below:

INSERT INTO EMPLOYEE VALUES(SEQ_EMPLOYEE_ID.nextval, 'Convallis','Programmer');
INSERT INTO EMPLOYEE VALUES(SEQ_EMPLOYEE_ID.nextval, 'Sollicitudin','Programmer');
INSERT INTO EMPLOYEE VALUES(SEQ_EMPLOYEE_ID.nextval, 'Quisque','Programmer');

Still we are doing some manual stuff by getting next value form sequence and inserting it to ID column while inserting data into EMPLOYEE table. We can make it more effective by writing a Trigger which will insert value to ID column automatically every time you insert data into EMPLOYEE table.

Achieving auto increment using Trigger
Create a Trigger for inserting value to ID column before insert into EMPLOYEE table.

CREATE TRIGGER TRIG_EMPLOYEE_ID
BEFORE INSERT ON EMPLOYEE  for each row  
WHEN (new.id is null)  

begin
select SEQ_EMPLOYEE_ID.nextval into :new.id from dual;
end;

Now all we need to care about is inserting values of NAME and DESIGNATION columns while inserting data into EMPLOYEE table because value of ID column will be added by TRIG_EMPLOYEE_ID trigger. See INSERT statements below:

INSERT INTO EMPLOYEE (NAME, DESIGNATION) VALUES('Convallis','Programmer');
INSERT INTO EMPLOYEE (NAME, DESIGNATION) VALUES('Sollicitudin','Programmer');
INSERT INTO EMPLOYEE (NAME, DESIGNATION) VALUES('Quisque','Programmer');

Oracle® database administrator's guide 11g release 1
Managing Tables
Managing Sequences
Enabling and Disabling Triggers