Identity Columns in Oracle

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:

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:

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:

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

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.

Previous Post
Defragmentation Proven to Completely Fix Performance Issues
Next Post
Find Corrupted Indexed Views with DBCC CHECKDB

6 Comments. Leave new

  • 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.

    • 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.

  • 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!

    • 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.

  • 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

Menu
{"cart_token":"","hash":"","cart_data":""}