Oracle 12c new features: Handling sequence as default value for columns

Oracle 12c enhanced the defaulting methods of table columns. Now, a column can default to a sequence. In addition, a column can have a default value which will be used only when the column is NULL.

Before Oracle 12c, if a column is expected to pull values from a sequence, it must be a done in a trigger or a procedure assigned in the program unit. In 12c, a sequence can be directly assigned to a column in the table.

In the below illustration, we create a table T with three columns. Column ‘X’ is defaulted to a sequence S, while column Z defaults to 13 when NULL.

sql> create sequence s;
Sequence created.

sql> create table t
( x number default s.nextval primary key,
  y number,
  z number default on null 13 );
Table created.

sql> insert into t (y,z) values ( 55, NULL );
sql> insert into t (y,z) values ( 100, NULL );
sql> insert into t (x,y,z) values (-3,-2,-3);
sql> select * from t;

X          Y          Z
———-      ———-       ———-
1         55         13
2         100        13
-3        -2         -3

This feature will standardize numeric primary key also it will reduce the use of the trigger and decode function for setting up default values.