IDENTITY Columns: Now with Oracle12c

Identity columns generate  unique numeric value for each row in a table. This option was available on DB2 since version 7 and is now available with Oracle 12c too.  Hooray !!!!

You can define identify columns with ALWAYS or DEFAULT option of CREATE/ALTER TABLE command.

ALWAYS: Oracle Database always uses the sequence generator to assign a value to the column and does not allow the user  to update or insert the identity column value.

BY DEFAULT: Similar to ALWAYS but  permits the user to explicitly assign a value using insert and update.

Using “BY DEFAULT” as opposed to “ALWAYS” does not guarantee uniqueness as the values can be updated by user.  The “ALWAYS”  option pretty much does what a primary key or unique key or unique index does with out having to create them explicitly. Use this to create uniqueness at your own peril.

How is identify clause different from Sequences?

An identity column has a  1-1 relationship  with tables whereas sequences are not tied to any single table. Therefore with sequences, the same value can be used across several tables. Also most of the sequence options like start value, max values etc can be defined with identity column option.

Example to illustrate 1-1 relationship between identity columns and tables

In  this example, I am creating 2 tables with similar structure and inserting data in both the table

SQL> CREATE TABLE EMP1
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL> SQL> CREATE TABLE EMP2
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL>
SQL>
SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL>  INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL>  INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> SELECT * FROM EMP1;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

SQL> SELECT * FROM EMP2;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

Example to illustrate DEFAULT option not preserving the uniqueness

In  this example, I am creating a  table with identify column (DEFAULT option) and therefore allows updates to  identity column  leading to duplicate values

SQL>
SQL> CREATE TABLE EMP4
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL>
SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> SELECT * FROM EMP4;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

SQL> UPDATE EMP4 SET EMPNO=2 WHERE EMPNO=1;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL>  SELECT * FROM EMP4;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
2 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

Example to illustrate adding new identity columns  to existing tables.

When you add a new identity column to an existing  table all existing rows are updated using
the sequence generator. The order in which a value is assigned to each existing row cannot be determined.

Also you can change an existing column to identity column

CREATE TABLE EMP3
(
EMPNO         NUMBER(9)        PRIMARY KEY,
EMPNAME      VARCHAR2(15)    NOT NULL,
DEPT         NUMBER(4)       ,
PHONENO       NUMBER(10)      ,
HIREDATE      DATE            ,
SALARY        NUMBER(9, 2)
)
TABLESPACE USERS;

SQL>
SQL> SELECT * FROM EMP3;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
101 TIM                     10 6309351100 13-AUG-13      50000
102 TIM                     10 6309351100 13-AUG-13      50000
103 TIM                     10 6309351100 13-AUG-13      50000

SQL>
SQL>

SQL>  ALTER TABLE EMP3 ADD (NEW_EMPNO NUMBER(9) GENERATED ALWAYS AS IDENTITY);

Table altered.

SQL>  SELECT * FROM EMP3;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY  NEW_EMPNO
———- ————— ———- ———- ——— ———- ———-
101 TIM                     10 6309351100 13-AUG-13      50000          1
102 TIM                     10 6309351100 13-AUG-13      50000          2
103 TIM                     10 6309351100 13-AUG-13      50000          3

Modifying existing column to identify column is not supported

SQL> ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED ALWAYS AS IDENTITY);
ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED ALWAYS AS IDENTITY);
*
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

SQL>
SQL> ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED BY DEFAULT AS IDENTITY);
ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED BY DEFAULT AS IDENTITY)
*
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

About these ads

Tagged: , , , ,

2 thoughts on “IDENTITY Columns: Now with Oracle12c

  1. […] Using Identity Columns […]

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 59 other followers

%d bloggers like this: