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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
1 2 3 4 |
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:
1 2 3 4 |
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:
1 2 3 4 |
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.
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.
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
ZXC – for questions, head on over to http://dba.stackexchange.com.