Create auto-increment or auto-generated field in Oracle

The auto-increment attribute is used in the primary key field of the database table to increment the field automatically when any new record is inserted in the table and this fields contains the numeric data. The auto-increment feature is not available in the table of the Oracle database like other databases. But this feature can be implemented in the Oracle by creating sequence or other way. The oracle sequence is one type of database object that can be used in the field of the table to generate the sequential integer number in ascending or descending order. The way of creating the sequence and using in the tables of the Oracle database have been shown in this tutorial.

Syntax:

CREATE SEQUENCE seq_name
[INCREMENT BY interval]
[START WITH starting_number]
[MAXVALUE max | NOMAXVALUE]
[MINVALUE min | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache | NOCACHE]
[ORDER | NOORDER];

According to the above syntax, the interval between two numbers in sequence can be set by the interval value and the default value is 1. The first number in the sequence can be set by the starting_number and the default value is 1. The maximum and the minimum number of the sequence can be set by the max and the min value. The circular sequence number can be generated by the CYCLE option and the default is NOCYCLE. The sequence value can be stored in the memory by using cache. The ORDER option is used to generate the number based on the instruction.

Create a simple sequence:

CREATE SEQUENCE seq_num1;

A sequence named SEQ_NUM1 will be created inside the Sequence folder like the following image.

Create a sequence with other options:

CREATE SEQUENCE seq_num2
INCREMENT BY 5
START WITH 0
MINVALUE 0
MAXVALUE 50;

A sequence named SEQ_NUM2 will be created inside the Sequence folder like the following image.

Use of sequence in the table:

Run the following SQL to create a table named products.

CREATE TABLE products(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    PRICE NUMBER NOT NULL
);

Run the following SQL commands to insert three records by using the SEQ_NUM1.

INSERT INTO products(ID, NAME, PRICE) VALUES(seq_num1.NEXTVAL, 'Monitor', 80);
INSERT INTO products(ID, NAME, PRICE) VALUES(seq_num1.NEXTVAL, 'Mouse', 10);
INSERT INTO products(ID, NAME, PRICE) VALUES(seq_num1.NEXTVAL, 'Keyboard', 50);

The table will contain the following data after inserting the records.

Run the following SQL commands to delete all previous records and insert two records by using the SEQ_NUM2 sequence.

DELETE FROM products;
INSERT INTO products(ID, NAME, PRICE) VALUES(seq_num2.NEXTVAL, 'Monitor', 80);
INSERT INTO products(ID, NAME, PRICE) VALUES(seq_num2.NEXTVAL, 'Mouse', 10);

The table will contain the following data after inserting the records.

Create auto-increment field without sequence:

Run the following SQL commands to drop the table and again create the table with the identity option that will generate the sequential number automatically for the primary key field..

DROP TABLE products;
CREATE TABLE products(
    ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    PRICE NUMBER NOT NULL
);

Run the following SQL commands to insert two records into the table without using any sequence like before.

INSERT INTO products(NAME, PRICE) VALUES('HDD', 100);
INSERT INTO products(NAME, PRICE) VALUES('Printer', 50);

The table will contain the following data after inserting the records.

Exercise:

Create a table with the sequence that will generate the value of the primary key from 3 with the internal 7.