Oracle | Identity Columns

Oracle Identity Columns Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Identity Columns in Oracle bring to the Oracle Database a capability that looks provides analogous functionality to Identity Columns in other database products such as Informix, SQL Server, and Sybase. The process by which they work is to generate a sequence object and assign it to generate a column default. Identity Columns offer no functionality superior or enhanced over that available using a sequence object.

Data Dictionary Objects

ALL_TAB_IDENTITY_COLS	IDNSEQ$	TAB$
CDB_TAB_IDENTITY_COLS	SEQ$	USER_TAB_IDENTITY_COLS
DBA_TAB_IDENTITY_COLS

Exceptions

Error Code	Reason
ORA-32795	cannot insert into a generated always identity column

System Privileges

ALTER ANY TABLE	CREAT TABLE	DROP ANY TABLE
CREATE ANY TABLE	IDNSEQ$

GENERATED ALWAYS Demo

Generated Always CREATE TABLE <table_name> ( <column_name> <column_data_type> GENERATED ALWAYS AS IDENTITY [ INCREMENT BY <integer> START WITH <integer> MAXVALUE <integer> / NOMAXVALUE MINVALUE <integer> / NOMINVALUE CYCLE / NOCYCLE CACHE <#> / NOCACHE ORDER / NOORDER]), <column_name> <column_data_type>);

CREATE TABLE t1 (

      rid NUMBER GENERATED ALWAYS AS IDENTITY,

      col VARCHAR2(3));

      

      desc t1

      

      Name   Null?      
      Type

      -----  ---------  -----------

      RID    NOT NULL   NUMBER

      COL               
      VARCHAR2(3)

      

      SELECT dbms_metadata.get_ddl('TABLE','T1',USER) from dual;

      

      DBMS_METADATA.GET_DDL('TABLE','T1',USER)

      ------------------------------------------------------------------------------

      CREATE TABLE "UWCLASS"."T1"

      ( "RID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 
      9999999999999

      999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE 
      NOKEEP N

      OSCALE NOT NULL ENABLE,

      "COL" VARCHAR2(3)

      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

      NOCOMPRESS LOGGING

      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

      TABLESPACE "UWDATA"

      

      SELECT table_name

      FROM user_tables

      WHERE has_identity = 'YES';

      

      TABLE_NAME

      ------------------------------

      T1

      QNAME

      

      SELECT column_name, identity_column

      FROM user_tab_cols

      WHERE table_name = 'T1';

      

      COLUMN_NAME  IDE

      ------------ ---

      RID          YES

      COL          NO

      

      INSERT INTO t1 (col) VALUES ('A');

      INSERT INTO t1 (col) VALUES ('B');

      INSERT INTO t1 (col) VALUES ('C');

      

      SELECT * FROM t1;

      

      desc dba_tab_cols

      desc dba_tab_columns

      

      SELECT table_name, column_name, default_on_null, identity_column

      FROM dba_tab_cols

      WHERE table_name = 'T1';

      

      TABLE_NAME  COLUMN_NAME  DEF IDE

      ----------- ------------ --- ---

      T1           RID         NO YES

      T1           COL         NO NO

      

      desc user_tab_identity_cols

      

      SELECT * FROM sys.idnseq$;

      

        OBJ#  INTCOL#  SEQOBJ#  STARTWITH

      ------ -------- -------- ----------

       77284        1    77285          1

      

      SELECT * FROM sys.seq$

      WHERE obj# IN (

        SELECT seqobj#

        FROM sys.idnseq$);

      

      SELECT data_default

      FROM dba_tab_cols

      WHERE table_name = 'T1';

      

      DATA_DEFAULT

      -------------------------------

      "C##SH"."ISEQ$$_77284".nextval

      

      CREATE TABLE t2 (

      rid NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 2,

      col VARCHAR2(3));

      

      INSERT INTO t2 (col) VALUES ('A');

      INSERT INTO t2 (col) VALUES ('B');

      INSERT INTO t2 (col) VALUES ('C');

      

      SELECT * FROM t2;

      

           RID COL

      -------- ---

            1  A

            3  B

            5  C

      

      INSERT INTO t2 (rid, col) VALUES (42, 'C');

      *

      ERROR at line 1:

      
      ORA-32795: cannot insert into a generated always identity column

      

      DROP TABLE t1 PURGE;

      DROP TABLE t2 PURGE;

      

      CREATE TABLE t3 (

      rid NUMBER GENERATED ALWAYS AS IDENTITY 

       INCREMENT BY 2

       START WITH 100

       MAXVALUE 110

       MINVALUE 100

       CYCLE

       CACHE 5

       NOORDER,

      col VARCHAR2(3));

      

      SELECT * FROM sys.seq$

      WHERE obj# IN (

        SELECT seqobj#

        FROM sys.idnseq$);

      

      INSERT INTO t3 (col) VALUES ('A');

      INSERT INTO t3 (col) VALUES ('B');

      INSERT INTO t3 (col) VALUES ('C');

      INSERT INTO t3 (col) VALUES ('D');

      INSERT INTO t3 (col) VALUES ('E');

      INSERT INTO t3 (col) VALUES ('F');

      

      SELECT * FROM t3;

      

      INSERT INTO t3 (col) VALUES ('G');

      

      SELECT * FROM t3;

      

      DROP TABLE t3 PURGE;

GENERATED BY DEFAULT Demo

Generated By Default CREATE TABLE <table_name> ( <column_name> <column_data_type> GENERATED BY DEFAULT AS IDENTITY [ INCREMENT BY <integer> START WITH <integer> MAXVALUE <integer> / NOMAXVALUE MINVALUE <integer> / NOMINVALUE CYCLE / NOCYCLE CACHE <#> / NOCACHE ORDER / NOORDER]), <column_name> <column_data_type>);

CREATE TABLE t4 (

      rid NUMBER GENERATED BY DEFAULT AS IDENTITY,

      col VARCHAR2(3));

      

      INSERT INTO t4 (col) VALUES ('A');

      INSERT INTO t4 (col) VALUES ('B');

      INSERT INTO t4 (col) VALUES ('C');

      

      SELECT * FROM t4;

      

      SELECT table_name, column_name, default_on_null, identity_column

      FROM dba_tab_cols

      WHERE table_name = 'T4';

      

      SELECT * FROM sys.idnseq$;

      

      SELECT * FROM sys.seq$

      WHERE obj# IN (

        SELECT seqobj#

        FROM sys.idnseq$);

      

      SELECT data_default

      FROM dba_tab_cols

      WHERE table_name = 'T4';

      

      CREATE TABLE t5 (

      rid NUMBER GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 10,

      col VARCHAR2(3));

      

      INSERT INTO t5 (col) VALUES ('A');

      INSERT INTO t5 (col) VALUES ('B');

      INSERT INTO t5 (col) VALUES ('C');

      

      SELECT * FROM t5;

      

      INSERT INTO t5 (rid, col) VALUES (42, 'C');

      

      SELECT * FROM t5;