2 Ways To Create Auto Increment Column In Oracle (Primary Key)

2019-11-02T18:03:37CET

We can create Auto increment columns in oracle by using IDENTITY columns in Oracle 12c. And in Oracle Database does not have any exisiting feature to auto increment column values in table schema until Oracle 12c (mid 2014).

Auto increment columns widely used for auto-generating values for primary keys in Database tables.Most of the databases like SQL server etc have existing features to create auto increment columns.

In Oracle 12c they introduced IDENTITY columns which allows users to create auto increment columns.

In previous versions of Oracle this functionality developed using the combination of Sequences and Triggers.

In this tutorial I am going to explain these two ways to implement auto increment coulmns in Oracle database tables with examples,pros and cons of each method.

On this page

Create Auto increment column in oracle By using IDENTITY Column example

Create an IDENTITY column by using following syntax

CREATE TABLE auto_increment_column_table (
 auto_increment_column_id NUMBER GENERATED ALWAYS AS IDENTITY,
 auto_increment_column_description VARCHAR2(50)
);

Now we can insert rows into the table

INSERT INTO auto_increment_column_table (auto_increment_column_desc) 
VALUES ('Test Data');
--1 rows inserted

We have three options to create IDENTITY Column

ALWAYS
BY DEFAULT
BY DEFAULT ON NULL

ALWAYS AS IDENTITY Example

We are using ALWAYS to force the use of IDENTITY. If we try to insert value into the IDENTITY column it will throw error saying cannot insert into a generated always identity column.

INSERT INTO auto_increment_column_table 
(auto_increment_column_id, auto_increment_column_desc) 
VALUES (NULL, 'Null Column');

INSERT INTO auto_increment_column_table 
(auto_increment_column_id, auto_increment_column_desc) 
VALUES (999, 'Manually inserting auto increment column');

--ORA-32795: cannot insert into a generated always identity column

BY DEFAULT AS IDENTITY Example

Create IDENTITY column with BY DEFAULT option to insert values into IDENTITY column by referencing it in insert statement.

CREATE TABLE auto_increment_default_column (
 auto_increment_column_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 auto_increment_column_desc VARCHAR2(50)
 );

INSERT INTO auto_increment_default_column
(auto_increment_column_desc) VALUES ('Test Data');

INSERT INTO auto_increment_default_column 
(auto_increment_column_id, auto_increment_column_desc)
 VALUES (999, 'Manually inserting auto increment column');

INSERT INTO auto_increment_default_column 
(auto_increment_column_desc) VALUES ('Second Test Data');

If you are specifying identity column in insert statment then it will insert whatever value we given if not then it will insert automatically incremented value into the IDENTITY column.

But we cannot insert NULL value it will throw error

INSERT INTO auto_increment_default_column 
(auto_increment_column_id, auto_increment_column_desc)
 VALUES (NULL, 'Manually inserting auto');

cannot insert NULL into 
("DEPLOY_ADMIN"."AUTO_INCREMENT_DEFAULT_COLUMN"."AUTO_INCREMENT_COLUMN_ID")

BY DEFAULT ON NULL AS IDENTITY Example

Use BY DEFAULT ON NULL option to create identity column to use auto incremented value if you specily NULL in identity column in insert statement.

CREATE TABLE auto_increment_default_null (
 auto_increment_column_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 auto_increment_column_desc VARCHAR2(50)
 );

INSERT INTO auto_increment_default_null 
(auto_increment_column_desc) VALUES ('Test Data');

INSERT INTO auto_increment_default_null 
(auto_increment_column_id, auto_increment_column_desc)
 VALUES (1000, 'Manually inserting auto increment column');

INSERT INTO auto_increment_default_null 
(auto_increment_column_desc) VALUES ('Second Test Data');

INSERT INTO auto_increment_default_null 
(auto_increment_column_id, auto_increment_column_desc)
 VALUES (NULL, 'Not Specified identity column');

Additionally we can change the initial value of identity column and interval between the auto generated values by using following options. The default initial value and interval values for auto increment identity columns equals to 1.

START WITH initial_value
INCREMENT BY interval_value

 CREATE TABLE identity_startwith (
 id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10,
 description VARCHAR2(100) not null
 );

 CREATE TABLE identity_startwith_incr (
 id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
 START WITH 10 INCREMENT BY 10,
 description VARCHAR2(100) not null
 );

In first table IDENTITY_STARTWITH the identity column starts with the value 10. and incremented by 1. Where as in second table IDENTITY_STARTWITH_INCR auto incemented column starts with 10 and incremented by 10.

When you create an identity column, Oracle generates 20 auto increment values before hand for performence reasons and Oracle recommends to include CACHE clause greater than the default of 20 to improve the performance.

CREATE TABLE auto_increment_table_cache
(auto_increment_column_id NUMBER GENERATED ALWAYS AS IDENTITY CACHE 30, 
auto_increment_column_description VARCHAR2(50));

Create auto increment column in oracle By using Sequences and Triggers Example

In earlier versions of Oracle we used to create auto increment columns using Sequences and Triggers.

Create a table and add primary key to that table

CREATE TABLE auto_increment_tb(
 id NUMBER(10) NOT NULL,
description VARCHAR2(100) NOT NULL
);

And then add primary key constraint

ALTER TABLE auto_increment_tb
 ADD (
 CONSTRAINT auto_increment_tb_pk PRIMARY KEY (id)
 );

Now we will create a sequence to generate unique auto incremented values.

A sequence is a data object that can be used by multiple users to generate auto increment values(no duplicate values will be generated).

CREATE SEQUENCE auto_increment_tb_seq;

We created sequence and but we are not using it. We will add a TRIGGER on table insert event.

CREATE OR REPLACE TRIGGER auto_increment_tb_insert
 BEFORE INSERT ON auto_increment_tb
 FOR EACH ROW
BEGIN
 SELECT auto_increment_tb_seq.nextval
 INTO :new.id
 FROM dual;
END;

Here we are creating a trigger named auto_increment_tb_insert which will be fired on each insertion on table auto_increment_tb.

We are getting next auto incremented value from created sequence by selecting auto_increment_tb_seq.nextval and inserting it into :new row of table ID column.

Best way to create auto increment column in oracle

Which is best way to insert auto incremented values into table? IDENTITY column or by using sequences and triggers?

Accroding oracle by using IDENTITY column in Oracle 12c is best way to create auto increment column than the older way of using Sequence and Triggers.

Limitations of IDENTITY Coulmn

We can add only one IDENTITY column per table.
We can add identity_clause only on Numeric datatype columns not on User-defined data types.
We cannot add DEFAULT clause in column defincation if we use identity_clause.
We can create one table from another by adding a AS SELECT statement at the end of the CREATE TABLE for example “create table new_emp AS SELECT * from emp”; New table wont inherit IDENTITY property on column.
When you add an identity_clause, then NOT NULL constraint and NOT DEFERRABLE constraint are added by default (implicitly specified). If you add an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE, then an error will occur.

Create Auto increment column in oracle By using IDENTITY Column example #

ALWAYS AS IDENTITY Example #

BY DEFAULT AS IDENTITY Example #

BY DEFAULT ON NULL AS IDENTITY Example #

Create auto increment column in oracle By using Sequences and Triggers Example #

Best way to create auto increment column in oracle #