Auto-Increment Column Oracle

Do you need to create an Auto Increment Column in a Oracle Table and you do not know how? No worries, you are in right place. This is not as simple as in MySQL or other Relational Database Management System where you just define Auto-Increment column during or after table creation and you are done. Everything is taken care after that. Oracle does not work that way, (wait it may be in 12c). it takes few additional few steps to get this accomplished which we will discuss and create a Table with Auto Increment Column. Also, see what Oracle 12c has to offer you for Auto-Increment Column (see somewhere below!)

Oracle Version 11g and Prior Releases

How to generate Auto Increment  ID for primary key value column on Oracle for version 11g and prior releases? It is a multiple steps without any complex steps, even a cave man can do it after these instruction.

We will need a table, sequence and trigger to make this happen. Lets begin with creating a table.

Table:

CREATE TABLE my_table 
  ( 
     id     NUMBER, 
     detail VARCHAR2(55) 
  ); 

Sequence:
The sequence will be used for ID number for my_table table.

--create sequence to use for the id number for test table
  CREATE SEQUENCE test_seq 
  START WITH 1 
  INCREMENT BY 1 
  NOMAXVALUE; 

Verify the Sequence:

SELECT sequence_name 
FROM   user_sequences 
WHERE  sequence_name LIKE 'TEST_SEQ'; 

The output will display the sequence details like sequence start, increment, max value etc.

Create Trigger:
This trigger will generate new value in a Sequence and Inserts into the ID column whenever an Insert statement is fired on my_table.

CREATE OR replace TRIGGER test_trigger 
  BEFORE INSERT ON my_table 
  FOR EACH ROW 
BEGIN 
    SELECT test_seq.NEXTVAL 
    INTO   :new.id 
    FROM   dual; 
END; 
/ 

Verify your Trigger creation

SELECT * 
FROM   user_triggers 
WHERE  trigger_name LIKE 'TEST%'; 

Test to see if the sequence is working as designed. Generating current and next value for a sequence

 --Current value 
SELECT test_seq.CURRVAL 
FROM   dual; 

--Next value 
SELECT test_seq.NEXTVAL 
FROM   dual; 

Insert Syntax for  Table with Auto Increment Column

-- Insert Data -- keeping id null 
INSERT INTO my_table 
            (detail) 
VALUES     ('Detail_1'); 

COMMIT; 

Checking your Insert statement and see if Auto Increment Column is working as designed

SELECT * 
FROM   my_table; 

I have one more ways to Insert data to a table Auto Increment column as a bonus

INSERT INTO my_table 
VALUES     (test_seq.NEXTVAL, 
            'Detail_2!'); 

You may need to verify it again using Select statement for above statements after committing the changes.

Oracle 12c and future releases

Lets see how Oracle Improved this not so complex multiple-steps process into "that was easy!!" process.

Create Table

CREATE TABLE employee 
             ( 
                          emp_id generated by DEFAULT AS identity, 
                          emp_name VARCHAR2(25) 
             );

This is much like MySQL and SQL Server database. You can easily use the insert statement which takes care of auto increment column.

Insert Statement

--Insert Statement 
INSERT INTO employee 
            (emp_name) 
VALUES      (‘john’); 

COMMIT;

The above process is simple but you don't have a control on the start, next and max value of increment column. There is another two steps process to accomplish the same result but you have more control over the sequence number and I like something I have control over.

--Create Sequence 
CREATE SEQUENCE test_seq 
  START WITH 1 
  INCREMENT BY 1 
  NOCYCLE; 

--Assign sequence nextval as a default value for a column 
CREATE TABLE test_tab 
  ( 
     id NUMBER DEFAULT test_seq.NEXTVAL PRIMARY KEY 
  ); 

This is how you create Auto Increment Columns on Oracle 12c or prior versions. How many Auto Increment Columns are you going to create today?