Search

Top 60 Oracle Blogs

Recent comments

Oracle 12c SQL IDENTITY Columns and Default SEQUENCES

As promised, here is the first in a series of posts dealing with Oracle 12c new features.

Creating new table rows often requires assigning a key value. In the past, it has been common to use an Oracle SEQUENCE to generate key values using an Insert trigger. Oracle 12c provides two new options: IDENTITY columns and SEQUENCEs used as column default values.

IDENTITY Columns

IDENTITY columns are new to Oracle, but, not new to the database world. IDENTITY columns use an Oracle SEQUENCE “under the covers” and their creation is automatic rather than manual. Should a table be dropped and recreated, the IDENTITY value starts over again.

Oracle has had SEQUENCES for years; the IDENTITY column allows use of a SEQUENCE as part of a column definition (much like some competitor databases):

  • Use “GENERATED AS IDENTITY” clause
  • Default starts with 1 increments by 1
  • May set values using START WITH and INCREMENT BY
  • IDENTITY column resets if table is dropped and recreated

IDENTITY Example 1

In this example the IDENTITY defaults to a starting value of one and an increment of one:

create table id_test1
(id number generated as IDENTITY, col1 varchar2(10));
--
insert into id_test1 (col1) values ('A');
insert into id_test1 (col1) values ('B');
insert into id_test1 (col1) values ('C');
--
select * from id_test1;
ID          COL1
---------- ----------
  1         A
  2         B
  3         C 

IDENTITY Example 2

In this example the IDENTITY column’s starting value and increment are specified:

create table id_test1
(id number generated as IDENTITY ( start with 10 increment by 11), col1 varchar2(10));
--
insert into id_test1 (col1) values ('A');
insert into id_test1 (col1) values ('B');
insert into id_test1 (col1) values ('C');
--
select * from id_test1;
ID          COL1 
---------- ---------- 
  10         A
  21         B
  32         C 

Enhanced Column DEFAULT using SEQUENCE

 

Oracle 12c enhances the capabilities of column default settings in various ways: columns may be set to a default when NULL values are INSERTed and column default values may be based upon a SEQUENCE (.nextval or .currval).

Example Defaults

In this example note that a sequence is referenced as a column default eliminating the need for a trigger to assign sequence values. Column defaults may use NEXTVAL or CURRVAL (when appropriate):

drop sequence default_test_seq;
drop table default_test;create sequence default_test_seq start with 1 increment by 1;
--
create table default_test
(id number default default_test_seq.nextval not null,
 col1 varchar2(10) ,
 col2 varchar2(10)default on null 'N/A' not null);
--
insert into default_test (col1,col2) values ('A',null);
insert into default_test (col1) values ('B');
insert into default_test (col1,col2) values ('C','test');
--
select * from default_test;
 ID           COL1       COL2
---------- ---------- ----------
 1             A          N/A
 2             B          N/A
 3             C          test 

Conclusion

 

So there you have it; Oracle now provides two ways to eliminate the use of triggers to assign key values based upon sequences. IDENTITY columns have the advantage of being entirely contained in the TABLE definition; they also restart should a table be dropped and recreated. If it is important to control the value assigned a little more closely, then, assigning a column value with a SEQUENCE in the column default may be a great option. Both techniques allow use of a SEQUENCE to generate column values without a trigger. This is one of many features Oracle 12c provides to make the lives of developers, DBAs, and architects better.