Oracle 12c Improved Defaults

Introduction

Oracle version 12c introduced some very nice and useful enhancements for table column defaults. This includes the following enhancements:

Identity columns
Default value when using a sequence
Default value when NULL is inserted

Identity columns

When identity column is created for a table, it will automatically generate an auto increment value upon every row insertion. It has the following options:

ALWAYS (default option) - In this option, Oracle forces the usage of the sequence generator. If an insert or update statements reference the identity column, Oracle raises an error.

BY DEFAULT - This option allows to explicitly assign a value for the column as part of an INSERT or UPDATE statements.

BY DEFAULT ON NULL - In the previous option (BY DEFAULT), If you will attempt to reference the identity column, and assign it a NULL value, you will encounter an error. Using the BY DEFAULT ON NULL option tells Oracle to use the identity generator when there is an attempt to insert a NULL to the table.

ALWAYS

In the first demonstration, I will create a sample table named "PRODUCTS" with an identity column for the primary key, that uses the ALWAYS option.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED ALWAYS AS IDENTITY,
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);

SQL> insert into PRODUCTS(NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS(ID, NAME) values (2, 'Product1');
ORA-32795: cannot insert into a generated always identity column

SQL> insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
ORA-32795: cannot insert into a generated always identity column

As you can see in the above demonstration, when trying to reference the identity column, Oracle raises an error.

BY DEFAULT

In the next demonstration, a "BY DEFAULT" clause is used for the identity column, which allows us to explicitly assign a value for the identity column (as long as it is not NULL), as follows:

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED BY DEFAULT AS IDENTITY,
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);
Table created.

SQL> insert into PRODUCTS(NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (2, 'Product1');
1 row created.

SQL> insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
ORA-01400: cannot insert NULL into ("SYS"."PRODUCTS"."ID")

BY DEFAULT ON NULL

Now, we will specify the "BY DEFAULT ON NULL" clause, which tells Oracle to use the identity generator even if NULL value is explicitly assigned:

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);
Table created.

SQL> insert into PRODUCTS(NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (102, 'Product1');
1 row created.

SQL>  insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
1 row created.

SQL> select * from PRODUCTS;

        ID NAME
---------- --------------------
         1 Product1
       102 Product1
         2 Product2

When creating an identity column, Oracle "behind the scenes" creates a sequence. This means that all the options that available for sequences (e.g. START WITH , INCREMENT BY, MAX_VALUE, etc.) are available for identify columns as well, as you can see in the following example:

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 10 INCREMENT BY 2),
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);
Table created.

It is possible to query the [CDB|DBA|ALL|USER]_tab_identity_cols dictionary view to obtain all of the table identity columns, as follows:

SQL> select table_name,column_name,generation_type, identity_options, sequence_name 
     from user_tab_identity_cols;

TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS                    SEQUENCE_NAME
---------- ----------- ---------- ----------------------------------- ---------------
PRODUCTS   ID          BY DEFAULT START WITH: 10, INCREMENT BY: 2, MA ISEQ$$_176970
                                  X_VALUE: 99999999999999999999999999
                                  99, MIN_VALUE: 1, CYCLE_FLAG: N, CA
                                  CHE_SIZE: 20, ORDER_FLAG: N

Additional Comments

Identity columns are NOT NULL, i.e. you cannot assign a NULL value to identity column
As previously mentioned, when creating an identity column, Oracle automatically creates a sequence. This means that in order to use this feature, the use must have the CREATE SEQUENCE privilege.

Default value when using a sequence

Another enhacement in the improved defaults which was introduced in Oracle 12c, it the ability to use a sequence as a default value for a column. It can be either CURRVAL or NEXTVAL. The following is a demonstration of this feature:

SQL> CREATE SEQUENCE id_seq;
Sequence created.

CREATE TABLE PRODUCTS
(
   ID     NUMBER DEFAULT ID_SEQ.NEXTVAL,
   NAME   VARCHAR2(20)
);
Table created.

SQL> insert into PRODUCTS (NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (DEFAULT, 'Product2');
1 row created.

SQL>  insert into PRODUCTS (ID,NAME) values (NULL,'Product3');
1 row created.

SQL> select * from PRODUCTS;

        ID NAME
---------- --------------------
         1 Product1
         2 Product2
           Product3

As you can see in the above example, Oracle will use the sequence as a default value if the column is not referenced, or if it is referenced and the value "DEFAULT" is specified. When an explicit NULL value was inserted, the default sequence value is not being used, instead, a NULL was inserted to the table.

Additional Comments

Creating a table with a default sequence value, requires having a SELECT privilege on the sequence (in addition to the CREATE TABLE privilege)
Inserting a value to the table with a default sequence requires having a SELECT privilege on the sequence (in addition to the INSERT privilege on that table)

Default value when NULL is inserted

In order to use a default sequence number even when an explicit NULL is being inserted, you can use the ON NULL clause. Let us see a demonstration of this feature:

SQL>  DROP SEQUENCE id_seq;
Sequence dropped.

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

SQL> CREATE SEQUENCE id_seq;
Sequence created.

CREATE TABLE PRODUCTS
(
   ID     NUMBER DEFAULT ON NULL ID_SEQ.NEXTVAL,
   NAME   VARCHAR2(20)
);
Table created.

SQL> insert into PRODUCTS (NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (DEFAULT, 'Product2');
1 row created.

SQL> insert into PRODUCTS (ID,NAME) values (NULL,'Product3');
1 row created.

SQL> select * from PRODUCTS;

        ID NAME
---------- --------------------
         1 Product1
         2 Product2
         3 Product3

Summary

In earlier releases (prior to Oracle 12c), there was no straightforward way to specify an auto increment value for a column; in order to solve this challenge, a common solution was creating a sequence and a trigger that inserts the sequence next value upon every insert to the table. Starting with Oracle 12c, there are 2 options that can provide that - using sequence as a default value, and using an identity columns. Both options provide a better performance than the old trigger approach, and both are simpler to use.

Monday, February 15, 2016