LATEST TOPICS

Oracle Database 12c New Feature: Auto increment column value with DEFAULT clause

This article is follow up of my last article where I had discussed about the Identity Column. As I had mentioned, in Oracle database 12c; we have two new features available that facilitates auto increment of column values.

I had discussed one of the features called Identity Column in my last article.

Today, I would be discussing the second feature, where we would be using the DEFAULT clause of a table column to perform auto increment of column values.

 

Demonstration:

Starting from Oracle database 12c, Oracle allows the use of CURRVAL and NEXTVAL sequence pseudo columns as the DEFAULT value for a column.

The DEFAULT value takes effect, only when the default column is not referenced in the INSERT statement

Auto Increment column value with DEFAULT

To be able to use DEFAULT clause for auto increment of column values, we need to have a existing sequence as a prerequisite.

Lets say, If I want the default column value to start with 100 and increment by 1 with a max values of 10000000; I must create a sequence like the following one before creating the table.

SQL:labpa >create sequence seq_default start with 100 increment by 1 maxvalue 10000000;

Sequence created.

SQL:labpa >select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER from user_sequences where sequence_name='SEQ_DEFAULT';

SEQUENCE_NAM  MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
------------ ---------- ---------- ------------ -----------
SEQ_DEFAULT           1   10000000            1         100

Now, Lets create the table to use this sequence’s NEXTVAL pseudo column as DEFAULT value. In that way, it would facilitate auto increment of the column value.

SQL:labpa >create table t_ainc_def (
  2  id number(10) DEFAULT SEQ_DEFAULT.nextval,
  3  name varchar(10)
  4  );

Table created.

SQL:labpa >desc t_ainc_def
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER(10)
 NAME                             VARCHAR2(10)

Lets insert few records into the table


SQL:labpa >insert into t_ainc_def (name) values ('abbas');

1 row created.

SQL:labpa >insert into t_ainc_def (name) values ('fazal');

1 row created.

SQL:labpa >insert into t_ainc_def (name) values ('mohammed');

1 row created.

SQL:labpa >select * from t_ainc_def;

ID         NAME
---------- ----------
100        abbas
101        fazal
102        mohammed

As expected, the default column values are getting incremented with each insertion. We are done here, as our goal is reached (it was too simple). However, few more points are worth to know !!

 

Few more details about using DEFAULT clause

What happens, if we explicitly specify values or specify NULL value for the default column. Lets see…

----
---- when a value is explicitly specified for default column,
---- DEFAULT value gets skipped
SQL:labpa >insert into t_ainc_def values (10000,'abc');

1 row created.

----
---- NULL values are also allowed, provided NOT NULL constraint is not implemented explictly
SQL:labpa >insert into t_ainc_def values (NULL,'xyz');

1 row created.

SQL:labpa >select * from t_ainc_def;

 ID        NAME
---------- ----------
 100       abbas
 101       fazal
 102       mohammed
 10000     abc
           xyz

As can be seen from the exercise,

  • When default column is skipped in the INSERT statement,  the DEFAULT value is substituted for the column. Using this method with sequence pseudo column, we can implement the auto increment for the column value.
  • When a value is explicitly specified for the default column in the INSERT statement, the DEFAULT value is skipped for the column.
  • The DEFAULT clause doesn’t enforce NOT NULL constraint automatically unlike IDENTITY COLUMN. So, we are also able to INSERT; NULL values for the default column by skipping the DEFAULT value (as shown in the 3rd INSERT statement).

However, if we want to substitute NULL values for default column with the DEFAULT value, we have an option to specify ON NULL along with the DEFAULT clause (as show below).


SQL:labpa >create sequence seq_default_1 start with 100 increment by 1 maxvalue 10000000;

Sequence created.

SQL:labpa >create table t_ainc_def_1 (
 2 id number(10) DEFAULT ON NULL SEQ_DEFAULT_1.nextval,
 3 name varchar(10)
 4 );

Table created.

SQL:labpa >desc t_ainc_def_1
Name                    Null?    Type
----------------------- -------- ----------------
ID                      NOT NULL NUMBER(10)
NAME                             VARCHAR2(10)

Once we specify  ON NULL along with the DEFAULT clause, NOT NULL constraint is automatically enforced on the column.

Now, when we explicitly specify NULL values for the default column, it would substitute NULL with the DEFAULT value (as shown below).


SQL:labpa >insert into t_ainc_def_1 values (null,'abc');

1 row created.

SQL:labpa >insert into t_ainc_def_1 (name) values ('xyz');

1 row created.

SQL:labpa >select * from t_ainc_def_1;

ID         NAME
---------- ----------
100        abc
101        xyz

There are certain restrictions while using DEFAULT clause. A complete list can be found here

 

Conclusion:

If I compare Identity  Column vs DEFAULT clause for auto increment of column values. I would prefer Identity Column over DEFAULT clause for few of the noted reasons.

With Identity Column, Oracle internally will take care of the sequence creation and maintenance. However, with DEFAULT clause we need to manually mange the sequence.

With Identity column, I can guarantee that the column values are always generated by Oracle and explicit values are not allowed (with the help GENERATED ALWAYS AS IDENTITY clause) which is not the case with DEFAULT clause.

You might think of some more advantages of using Identity Column over DEFAULT clause !!

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed