How to create an AUTO_INCREMENT column in a table on Oracle DB using SQL?
- How-Tos FAQs
- February 1, 2019
Before Oracle 12c, we don’t have a direct method of generating an AUTO_INCREMENT column in a table. We need to use the combination of Sequences and Triggers. Now, we have two different ways to implement it.
Using IDENTITY column
There are three options on IDENTITY COLUMN
- BY DEFAULT AS IDENTITY
- ALWAYS AS IDENTITY
- BY DEFAULT ON NULL AS IDENTITY
IDENTITY column with option BY DEFAULT AS IDENTITY
In this option, if a value is given in an INSERT statement, it will be inserted. Otherwise, the value on the IDENTITY column will be generated automatically.
CREATE TABLE student (
student_id INT GENERATED BY DEFAULT AS IDENTITY,
student_name VARCHAR2(50)
);
-- IDENTITY column gets the data automatically
INSERT INTO student
(student_name) VALUES ('Jack');
-- Manually inserting value
INSERT INTO student
(student_id, student_name)
VALUES (100, 'Rose');
-- IDENTITY column gets the data automatically
INSERT INTO student
(student_name) VALUES ('John');
SELECT * FROM student
-- Output
student_id student_name
----------------------------
1 Jack --(auto value)
100 Rose --(manual value)
2 John --(auto value)IDENTITY column with option ALWAYS AS IDENTITY
In this option, the IDENTITY column must be inserted automatically. An error will be thrown if a manual value is tried to be inserted.
CREATE TABLE student (
student_id NUMBER GENERATED ALWAYS AS IDENTITY,
student_name VARCHAR2(50)
);
-- IDENTITY column gets the data automatically
INSERT INTO student
(student_name) VALUES ('Jack');
-- IDENTITY column gets the data automaticallyINSERT INTO student
(student_name) VALUES ('John');
SELECT * FROM student
#Output
STUDENT_ID STUDENT_NAME
--------------------------
1 Jack
2 John
-- Manually inserting value will give below error message
INSERT INTO student
(student_id, student_name)
VALUES (100, 'Rose');
-- Error Message
/*
SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 - "cannot insert into a generated always identity column"
*Cause: An attempt was made to insert a value into an identity column
created with GENERATED ALWAYS keywords.
*Action: A generated always identity column cannot be directly inserted.
Instead, the associated sequence generator must provide the value.
*/IDENTITY column with option ALWAYS AS IDENTITY
In this option, the IDENTITY column must be inserted automatically. An error will be thrown if a manual value is tried to be inserted.
-- drop table student
CREATE TABLE student (
student_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
student_name VARCHAR2(50)
);
-- Value not set
INSERT INTO student
(student_name) VALUES ('Jack');
-- Value set as NULL
INSERT INTO student(student_id, student_name)
VALUES (NULL,'Rose');
--Value set manually
INSERT INTO student(student_id, student_name)
VALUES (100,'John');
SELECT * FROM student
-- Output
STUDENT_ID STUDENT_NAME
-------------------------
1 Jack
2 Rose
100 RoseFor oracle versions older than 12c, we need to use SEQUENCE and TRIGGER.
Create SEQUENCE
Let us first create a table ‘student’ and a SEQUENCE to be used in the table.
CREATE TABLE student (
student_id INT,
student_name VARCHAR2(50)
);
CREATE SEQUENCE student_seq;Create Trigger
While creating the trigger, we specify the name of the table, the event on which the trigger should be called, and the column’s name on which the value needs to be inserted.
CREATE OR REPLACE TRIGGER trg_student
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
SELECT student_seq.nextval
INTO :new.student_id
FROM dual;
END;Now, let’s insert some value in the table.
INSERT INTO student(student_name)
VALUES ('Jack');
INSERT INTO student(student_name)
VALUES ('Rose');
-- Here, even though a manual value is inserted in the column, the column takes the value from the SEQUENCE
INSERT INTO student(student_id, student_name)
VALUES (100, 'John');
SELECT * FROM student;
-- Output
Student_id Student_Name
-------------------------
1 Jack
2 Rose
3 JohnUp Next:
Read What are the main differences between NOT IN vs NOT EXISTS in SQL?
