How to create Auto Increment column in Oracle

Last Updated on :April 18, 2021

In oracle, you can create Auto Increment column using sequence database object. Using Oracle sequence object you can generate unique value and assign that value to primary key.

The advantage of using sequence object is it prevents duplicate values being created simultaneously

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value; 

sequence_name – The name of the sequence you want to create

Example
Let’s create ORDERS table with primary key on order_id column

CREATE TABLE ORDERS
(
ORDER_ID         NUMBER(10) NOT NULL,
CUSTOMER_ID      NUMBER(10) NOT NULL,
DATE_ORDERED     DATE,
DATE_SHIPPED     DATE,
SALES_PERSION_ID NUMBER(10) ,
TOTAL            NUMBER(11),
ORDER_FILLED     VARCHAR2(1),
CONSTRAINT ORD_ID_PK PRIMARY KEY (ORDER_ID)
);

Now let’s create a sequence order_seq that will be used later to generate unique auto incremented value on order_id column.
This will create a sequence object orders_seq with start with 1 and each number would increment by 1.

CREATE SEQUENCE orders_seq
MINVALUE 1
MAXVALUE 9999999999999999
START WITH 1
INCREMENT BY 1;

You can use orders_seq.NEXTVAL to retrieve next value in sequence order. Let’s insert data into order table

INSERT
INTO ORDERS
(
ORDER_ID,
CUSTOMER_ID,
DATE_ORDERED,
DATE_SHIPPED,
SALES_PERSION_ID,
TOTAL,
ORDER_FILLED
)
VALUES
(
orders_seq.nextval,
201,
DATE '2017-06-30',
DATE '2017-06-30',
10,
8330 ,--total
'Y' --ORDER_FILLED
);

You can create a trigger that will populate primary key value (order_id) automatically using sequence.
Trigger will execute prior to INSERT into order table , orders_seq SEQUENCE is incremented and the new value will pass on to the primary column

Trigger Definition

CREATE OR REPLACE TRIGGER ORDER_ID_TRIGGER
BEFORE INSERT ON ORDERS
FOR EACH ROW
BEGIN
SELECT orders_seq.NEXTVAL
INTO   :new.ORDER_ID
FROM   dual;
END;
/

Let us insert the data to orders table. Here I am not generating order_id value explicitly. Before Inserting into ORDER table, ORDER_ID_TRIGGER will execute and new order_id will get stored

INSERT
INTO ORDERS
(
CUSTOMER_ID,
DATE_ORDERED,
DATE_SHIPPED,
SALES_PERSION_ID,
TOTAL,
ORDER_FILLED
)
VALUES
(
201,
DATE '2017-06-30',
DATE '2017-06-30',
10,
8330 ,--total
'Y' --ORDER_FILLED
);