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,
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');
Lets take a look at what is in the beermaid.beers table now:
SELECT beer_id, beer_name
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.
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.