• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: Identity Column

ORACLE 12c Release 1 – Identity Column

09 Tuesday Jul 2013

Posted by troroblog in ORACLE, ORACLE 12c Release 1

≈ Leave a comment

Tags

Identity Column, ORACLE, ORACLE 12c Release 1

For a long time the ORACLE community has waited for something like an auto-increment column on tables. With the latest release of ORACLE this feature was implemented – but what benefits do identity columns have over the good old trigger/sequence solution?

CREATE TABLE employees (employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY
                                           (START WITH 1 INCREMENT BY 1)
                       ,last_name   VARCHAR2(30) NOT NULL)
/

 A identity column is always generated to be NOT NULL.

desc employees

Name                             Null?    Type<
-------------------------------- -------- ------------------------------------
EMPLOYEE_ID                      NOT NULL NUMBER
LAST_NAME                        NOT NULL VARCHAR2(30)

Generate Options

There are 3 different options to generate the identity value

GENERATED BY DEFAULT

  • identity column is populated using the sequence if column is not passed by the insert statement
  • if NULL value is passed you get an error, as NULL is inserted and the column is generated to be NOT NULL

GENERATED BY DEFAULT ON NULL

  • identity column is read from the sequence if columns is not passed or no value is passed by the insert statement
  • same as if you had created a sequence and a default specification

GENERATED ALWAYS

  • if generated always is used as the option you are not allowed to pass a value for the identity column.

Primary Key Constraint

Is an identity column the primary key per default?

Using an identity (surrogate key) column as the primary key of a table makes perfect sense but ORACLE has not implemented an automatism for that, which is perfectly ok. If we want to use the identity column as primary key we have to do it on our own.

CREATE TABLE employees (employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY                                           (START WITH 1 INCREMENT BY 1)
                       ,last_name   VARCHAR2(30) NOT NULL
                       ,CONSTRAINT employees_pk PRIMARY KEY (EMPLOYEE_ID)
                       )
/

Identity Sequence

All identity column statement create a sequence with the name ISEQ$$_[ObjectNumber]. Where ObjectNumber is the number of the object the sequence serves for.

SELECT t.NAME AS TABLE_NAME
      ,c.name AS IDENTITY_COLUMN_NAME
      ,s.NAME AS SEQUENCE_NAME
  FROM            sys.IDNSEQ$ os
       INNER JOIN sys.obj$    t  ON (t.obj# = os.obj#)
       INNER JOIN sys.obj$    s  ON (s.obj# = os.seqobj#)
       INNER JOIN sys.col$    c  ON (    c.obj# = t.obj#
                                     AND c.col# = os.intcol#)
>/ 

TABLE_NAME                     IDENTITY_COLUMN_NAME           SEQUENCE_NAME
------------------------------ ------------------------------ ------------------------------
EMPLOYEES                      EMPLOYEE_ID                    ISEQ$$_93717

Drop Table

Dropping the table having an identity column will not affect the sequence as long as the recycle bin is not purged or the table is not dropped using a drop table … purge command. As soon as the recycle bin is purged the sequence is dropped too. The advantage of this behaviour is, that if a table is undropped using a flashback command the sequence does not need to be recreated as it is still around (and has the correct value).

INSERT INTO employees(last_name) VALUES ('HUBER');

COMMIT;

DROP TABLE employees;

FLASHBACK TABLE employees TO BEFORE DROP; 

INSERT INTO employees(last_name) VALUES ('FLASHBACK');

SELECT * FROM employees;

EMPLOYEE_ID LAST_NAME
----------- ------------------------------
          1 HUBER
          2 FLASHBACK

Rename identity sequence

While renaming the sequence was possible in the first releases of ORACLE 12c this does not longer work – which is a good thing.


Conclusions

Advantages:

  • Now, the declaration effort to be taken is smaller. We do not longer have to write trigger code to populate the surrogate key.
  • A default value may not become invalid – this makes the default value assignment more stable than triggers.
  • With the new default value ON NULL clause the default value will also be taken, when a NULL value is passed.
  • Sequence is dropped when the table it is used for is dropped – so no zombie-sequences will exist in the future. 

Disadvantages:

  • We do not have control over the name of the sequence.
  • I have seen that many surrogate key columns have been sourced using a single sequence. This will not be possible using the “IDENTITY COLUMN” feature.

 

Will I personally make use of the new “identity column” feature?

 Even though I am not really a friend of having objects with artificial names in the application schema I do not see a reason why I should create the sequence and the default assignment by my own.

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...