Tags

, ,

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.