Oracle 12 IDENTITY Columns

Hello everyone, today we wanted to discuss with you a feature of Oracle 12.1 that is very powerful and rarely used, the IDENTITY columns.

This functionality is implemented in most database engines and we finally have it available in Oracle. It would be similar to MySQL’s AUTO_INCREMENT column or SQL Server’s IDENTITY column. Its function is to insert a sequential value in a column every time a row is inserted in a table, it is usually used to generate primary keys. Its functionality is very similar to the use of sequences but its use is simpler.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

For its use, the create sequence permissions are necessary and the identity_options part is very similar to that of the sequences, so we will focus on the first part.

If we use ALWAYS we force the use of the IDENTITY column. If in the INSERT we use a value for that column or NULL we will get an error:

CREATE TABLE identity_table (
 id NUMBER GENERATED ALWAYS AS IDENTITY,
 description VARCHAR2(30)
);

SQL> INSERT INTO identity_table (description) VALUES ('Only description');

1 row created.

SQL> INSERT INTO identity_table (id, description) VALUES (NULL, 'ID=NULL and description');
INSERT INTO identity_table (id, description) VALUES (NULL, 'ID=NULL and description')
  *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> INSERT INTO identity_table (id, description) VALUES (999, 'ID=999 and description');
INSERT INTO identity_table (id, description) VALUES (999, 'ID=999 and description')
  *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

With the use of BY DEFAULT we can use the IDENTITY column in the INSERT and add values manually. If the inserted value is NULL, an error will be returned:

DROP TABLE identity_table PURGE;

CREATE TABLE identity_table (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 description VARCHAR2(30)
);

SQL> INSERT INTO identity_table (description) VALUES ('Only description');

1 row created.

SQL> INSERT INTO identity_table (id, description) VALUES (999, 'ID=999 and description');

1 row created.

SQL> INSERT INTO identity_table (id, description) VALUES (NULL, 'ID=NULL and description');
INSERT INTO identity_table (id, description) VALUES (NULL, 'ID=NULL and description')
  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."identity_table"."ID")

With the use ofBY DEFAULT ON NULL It also allows us to use NULL in INSERTs, although it is ignored:

DROP TABLE identity_table PURGE;

CREATE TABLE identity_table (
 id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 description VARCHAR2(30)
);

SQL> INSERT INTO identity_table (description) VALUES ('Only description');

1 row created.

SQL> INSERT INTO identity_table (id, description) VALUES (999, 'ID=999 and description');

1 row created.

SQL> INSERT INTO identity_table (id, description) VALUES (NULL, 'ID=NULL and description');

1 row created.

SQL> SELECT * FROM identity_table;

ID description
---------- ------------------------------
 1 description only
 999 ID=999 and description
 2 ID=NULL and description

When we query the system views it seems that we are using a normal sequence:

COLUMN object_name FORMAT A20

SELECT object_name, object_type
FROM user_objects;

OBJECT_NAME OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_92117 SEQUENCE
identity_table TABLE

2 rows selected.

To see the information about the IDENTITY columns we have to use the views[DBA|ALL|USER]_TAB_IDENTITY_COLS :

SET LINESIZE 100
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A15
COLUMN generation_type FORMAT A10
COLUMN identity_options FORMAT A50

SELECT table_name, 
 column_name,
 generation_type,
 identity_options
FROM all_tab_identity_cols
WHERE owner = 'TEST'
ORDER BY 1, 2;

IDENTITY Column Restrictions

The use of IDENTITY columns in Oracle has the following restrictions:

There can be only one IDENTITY column per table.
IDENTITY columns must be of numeric type, user-defined types are not allowed.
IDENTITY columns cannot have a DEFAULT clause.
When we use CREATE TABLE … AS SELECT the column of the new table will not be IDENTITY. If we want to keep the same structure we have to create the table first and then perform an INSERT AS SELECT.

We hope that the post will be useful to you and that you can use this new functionality. If you want us to analyze your case to see if it is feasible to use IDENTITY columns. Contact us without commitment.

See you in next innings.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn

More info at: https://www.oracle.com/lad/technical-resources/articles/sql/oracle-db-12c-columna-identity.html

IDENTITY Column Restrictions

Leave a Reply