Identity Columns – 12c edition

Oracle has made it easier to define unique table columns with IDENTITY columns.  These are table columns that have been enhanced to support the American National Standards (ANSI) SQL key word IDENTITY.  These columns are meant to provide a standards based approach to declaring automatically incrementing columns which will simplify application development and making the migration of DDL from other platforms,  like Sybase or MS SQL Server, easier and simpler.

Lets take a look at how we can use this new column with a create table statement.

CREATE TABLE beermaid.beers
(
beer_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
beer_name VARCHAR2(25)
)
TABLESPACE beers; 

Now lets put some data into beermaid.beers table:

INSERT INTO beermaid.beers (beer_name)
VALUES ('Samual Adams');
INSERT INTO beermaid.beers(beer_id, beer_name)
VALUES (null, 'Blue Moon');
COMMIT;

Lets take a look at what is in the beermaid.beers table now:

SELECT beer_id, beer_name
FROM beermaid.beers;
 

beerresults0.png

 

 

 

As you can see, the beer_id column has been incremented with every insert.  Greatly making it easier to increment an identity column without the need of a trigger to increment the next value of a sequence.  

This feature implements the auto increment by enhancing the DEFAULT or DEFAULT ON NULL semantics for use by SEQUENCE.NEXTVAL and SYS_GUID, which are built-in functions and implicit return of default values.

I’m sure as DBAs and developers become more away of IDENTITY columns, these types of columns will be well received and embraced by the masses.

Enjoy!

twitter: @curtisbl294

blog: http://dbasolved.com

About these ads

2 comments

  1. Dbcapoeira · · Reply

    This definitely makes it easier and simpler to create and id for a row. Here’s a question: When compared to sequences, how does this feature compare? Can I start with values other than 1 or limit it to a maximum value or is it simply there to provide auto increment functionality?

  2. The intent of the identity column is to help organizations who want to move off of other platforms (MS SQL Server, Sybase, etc.) over to Oracle. In reference to your question, I will have to try what you are asking and update you when I do.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

PeteWhoDoesNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

Follow

Get every new post delivered to your Inbox.

Join 1,229 other followers

%d bloggers like this: