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
Tagged: generated always as identity, generated always as identity oracle, generated by default as identity, generated by default as identity oracle, IDENTITY columns
TwelveC 
[…] Using Identity Columns […]
[…] Using Identity Columns […]