Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
I will try to explain the identity feature, which is my favorite innovation in Oracle 12c.
MS SQL and MYSQL users know the Identitiy feature. It is now also available in Oracle databases.
We used SEQUENCE when this feature wasn’t available. While adding records to the table, we would take the new value of this SEQUENCE. Trigger can also be created for this operation.
Oracle 12c offers 2 options for this process: the IDENTITY column and the SEQUENCE to be used as the default value of the column.
Identitiy Column:
The IDENTITY column is new on Oracle, but has been available for many years in other databases. Actually IDENTITY columns creates a SEQUENCE in the background . But this is done automatically. The IDENTITY value is reset when the table is dropped or re-created.
Oracle databases have SEQUENCE for many years. The IDENTITY column provides the use of a SEQUENCE as part of the column definition.
Usage:
• Specified as “GENERATED AS IDENTITY”.
• The default value starts from 1 and increases 1 by 1.
• Value can be assigned with START WITH and INCREMENT BY and the increment value can be set.
• The IDENTITY column is reset if the table is dropped or re-created.
Example 1:
In this example, the value of the column with IDENTITY starts from 1 and increases 1 by 1.
1 2 3 4 5 6 7 8 9 10 11 12 13 | create table table1 (id number generated as IDENTITY, NAME varchar2(50)); -- insert into table1 (adsoyad) values ('Ahmet Duruöz'); insert into table1 (adsoyad) values ('Kemal Duru'); insert into table1 (adsoyad) values ('Ceyhun Yılmaz'); -- select * from tablo1; ID NAME ---------- ---------- 1 Ahmet Duruöz 2 Kemal Duru 3 Ceyhun Yılmaz |
Example 2:
In this example, the initial value and increment value of the IDENTITY column are specified.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create table table2 (id number generated as IDENTITY ( start with 10 increment by 11), NAME varchar2(10)); -- insert into table2(isim) values ('Ahmet Duruoz'); insert into table2(isim) values ('Bekir Kilic'); insert into table2(isim) values ('Nurullah Cakir'); -- select * from table2; ID NAME ---------- ---------- 10 Ahmet Duruoz 21 Bekir Kilic 32 Nurullah Cakir |
A column with DEFAULT value is SEQUENCE:
In Oracle 12c, we can assign the default value to column in several ways:
- A default value can be assigned when column value is added as NULL.
- The default value of the column is given with a SEQUENCE (.nextval or .currval).
Example:
In this example, the default value of the id column is assigned as a sequence.
When the value of the surname column is NULL, it is set to “unidentified”. This means that we dont need a trigger to assign an automatic value to the column.
First we create a sequence:
1 | create sequence default_test_seq start with 1 increment by 1; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | create table table3 (id number default default_test_seq.nextval not null, name varchar2(10) , surname varchar2(10) default on null 'unidentified' not null); insert into table3(name,surname) values ('Ahmet',null); insert into table3(name) values ('Bekir'); insert into table3(name,surname) values ('Kerim','Oner'); select * from table3; ID NAME SURNAME ---------- ---------- ---------- 1 Ahmet unidentified 2 Bekir unidentified 3 Kerim Oner |
