Identity Columns in Oracle

Last Updated 9 years ago

Many databases have the ability to create automatically incremented numbers. In SQL Server, we can use an IDENTITY property on a column to get autoincrementing numbers. But what can do we in Oracle?

Sequences

Sequences work in all recent versions and editions of Oracle. The default way to do this is pretty simple:

CREATE TABLE my_table (
    id NUMBER PRIMARY KEY,
    whatever VARCHAR2(30)
);

CREATE SEQUENCE my_table_seq ;

CREATE OR REPLACE TRIGGER my_table_insert 
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    SELECT my_table_seq.nextval 
    INTO   :new.id
    FROM   dual ;
END ;

/* Or, if you hate triggers, you can make sure all inserts look like: */
INSERT INTO my_table (id, whatever)
VALUES (my_table_seq.nextval, 'Erik');

CREATE TABLE my_table (

id NUMBER PRIMARY KEY,

whatever VARCHAR2(30)

);

CREATE SEQUENCE my_table_seq ;

CREATE OR REPLACE TRIGGER my_table_insert

BEFORE INSERT ON my_table

FOR EACH ROW

BEGIN

SELECT my_table_seq.nextval

INTO :new.id

FROM dual ;

END ;

/* Or, if you hate triggers, you can make sure all inserts look like: */

INSERT INTO my_table (id, whatever)

VALUES (my_table_seq.nextval, 'Erik');

Now that SQL Server supports sequences, you could use sequences to help with portability between both systems, but there’s a gotcha – SQL Server’s next value syntax is different from Oracle’s. That means you’d need to use a trigger based solution if you didn’t want code to change.

Identities

What about identities? With SQL Server, we just mark a column as having an identity value. Magic! It’s done for us. Oracle is a commerical database, surely it has a magic option, right?

Oracle introduced identity columns with Oracle 12c. This lets developers keep using the code they’re used to. Check this out:

CREATE TABLE my_table (
    id NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    whatever VARCHAR2(30)
);

CREATE TABLE my_table (

id NUMBER GENERATED AS IDENTITY PRIMARY KEY,

whatever VARCHAR2(30)

);

This lets you keep using the same kind of inserts that you would normal use for SQL Server, with one execption. Oracle gives you some flexibility for generating identities – you can generate them ALWAYS (the default) or BY DEFAULT (when a value isn’t provided).

If you really want to mimic SQL Server’s identity behavior, including IDENTITY INSERT then you can create your table like this:

CREATE TABLE my_table (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    whatever VARCHAR2(30)
);

CREATE TABLE my_table (

id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

whatever VARCHAR2(30)

);

You can even go so far as to protect yourself from trying to insert NULL values into the table using the ON NULL clause:

CREATE TABLE my_table (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    whatever VARCHAR2(30)
);

CREATE TABLE my_table (

id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

whatever VARCHAR2(30)

);

Sequences and Identities in Oracle and SQL Server

If you want to make sure you can reuse as much code as possible between Oracle and SQL Server, identity columns are a safe bet. The vast majority of code won’t need to change and your application will be none the wiser.

Brent says: when people talk about porting their application from one database back end to another, this is a good example of how it’s so tricky. Sure, both databases have exactly the same functionality available, but even something as simple as an identity column is syntactically different between them.

Erik says: In case anyone’s wondering, it didn’t hurt getting inserted into an Oracle table. The bathrooms in Tablespace were very clean.

Defragmentation Proven to Completely Fix Performance Issues

Find Corrupted Indexed Views with DBCC CHECKDB

6 Comments. Leave new

Kevin Fries
April 2, 2015 11:33 am

Another topic on performance of sequences is that they can be a bottleneck and buffering of “next” sequence values via the cache clause is possible. Of course there may be legal/audit requirements about not having gaps.But for a process with hundreds of inserts it can be a lifesaver.

Reply
- Jeremiah Peschka
  April 2, 2015 12:02 pm
  
  That is a great point – with the sequence we can change around how many values are buffered in advance and get better performance but at the cost of gaps in the sequence.
  
  Reply
Prabin Baniya
April 2, 2015 11:41 am

Good Posting. This posting for only applies to Oracle 11g and prior version. With Oracle 12c, you just do exactly how you do that in SQL server. You just define the field as identity and you don’t need sequence to generate value for your identity column

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

You can find the detail on auto increment with Oracle on http://www.dbarepublic.com/2014/04/auto-increment-column-oracle.html

Thanks!

Reply
- Jeremiah Peschka
  April 2, 2015 12:05 pm
  
  Hi Prabin,
  
  If you’d read the whole article instead of rushing to post a link to your blog, you would see that I cover the use of the identity property, too.
  
  Reply
ZXC
April 29, 2016 1:40 pm

hi
how to get the identity column name from table.
i want to insert a row where i do not have information about identity column name

Reply
- Brent Ozar
  April 29, 2016 3:04 pm
  
  ZXC – for questions, head on over to http://dba.stackexchange.com.
  
  Reply