IDENTITY Oracle 12 Columns

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

This functionality is implemented in most database engines and we finally have it available from Oracle. It would be similar to the MySQL AUTO_INCREMENT column or the SQL Server IDENTITY column. Its function is to insert a sequential value in a column every time a row is inserted into 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_tabla (
 id NUMBER GENERATED ALWAYS AS IDENTITY,
 description VARCHAR2(30)
);

SQL> INSERT INTO identity_tabla (description) VALUES ('Description Only');

1 row created.

SQL> INSERT INTO identity_tabla (id, description) VALUES (NULL, 'ID=NULL and description');
INSERT INTO identity_tabla (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_tabla (id, description) VALUES (999, 'ID=999 and description');
INSERT INTO identity_tabla VALUES (id, description) VALUES (999, 'ID=999 and description')
  *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Using BY DEFAULT we can use the IDENTITY column in the INSERT and add values manually. If the value inserted is NULL it will return an error:

DROP TABLE identity_tabla PURGE;

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

SQL> INSERT INTO identity_tabla (description) VALUES ('Description Only');

1 row created.

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

1 row created.

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

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

DROP TABLE identity_tabla PURGE;

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

SQL> INSERT INTO identity_tabla (description) VALUES ('Description Only');

1 row created.

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

1 row created.

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

1 row created.

SQL> SELECT * FROM identity_tabla;

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

When we consult 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_tabla 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 Oracle 12 Columns

Identity column restrictions

Using IDENTITY columns in Oracle has the following restrictions:

  • There can be only one IDENTITY column per table.
  • IDENTITY columns must be numeric in 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 maintain the same structure we have to create the table first and then perform an INSERT AS SELECT.

We hope that you find the entry useful 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 obligation.

See you in future posts.

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

Comments are closed.