Auto Increment Column in Oracle

Before Oracle 11G, we could not been able to use auto increment column as such, but we would have to create a sequence and then we have to use trigger to insert the unique number. But in Oracle 11G, We can directly use sequence in the column, but still its not auto increment column. In Oracle 12C, Oracle have provide the actual auto increment column.

So we will see how to do it in Oracle 11G and Oracle 12C.

Let's try to do it in Oracle 11G:-

In oracle 11G, we need to first create a sequence, then we can use nextval psuedo columnn to insert the value.

Simple example is:-

CREATE SEQUENCE SEQUENCE_NAME 
MINVALUE MINIMUM_VALUE,
START WITH START_VALUE,
INCREMENT BY INCREMENT_VALUE,
CYCLE || NOCYCLE,
CACHE || NOCACHE;

Here:-

MINVALUE is the value from oracle will store as minimum value for that particular sequence.

START WITH is the value which is going to get inserted at the first time.

INCREMENT BY is the values by which you have to increase the next value to insert.

CYCLE  Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

CACHE Specify how many values of the sequence the database pre-allocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers.

Let's see the example:-

CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE,
NORECYCLE;

Now we have a sequence, let's try to create a table in Oracle 11G using the above sequence:-

CREATE TABLE EMPLOYEE
(
EMPNO NUMBER DEFAULT SEQ_TEST.NEXTVAL,
EMPNAME VARCHAR2(20),
SALARY NUMBER,
DEPTNO NUMBER
);

You need to mention DEFAULT clause while creating a table.

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);


In Oracle 12C, we create directly create auto increment column. We don't need to use sequence there.




Read Also:-  Auto Increment Column in Teradata