I will try to explain the identity feature, which is my favorite innovation in Oracle 12c.<\/p>\n
MS SQL and MYSQL users know the Identitiy feature. It is now also available in Oracle databases.<\/p>\n
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.<\/p>\n
Oracle 12c offers 2 options for this process: the IDENTITY column and the SEQUENCE to be used as the default value of the column.<\/p>\n
<\/p>\n
Identitiy Column:<\/h3>\n
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\u00a0 . But this is done automatically. The IDENTITY value is reset when the table is dropped or re-created.<\/p>\n
Oracle databases have SEQUENCE for many years. The IDENTITY column provides the use of a SEQUENCE as part of the column definition.<\/p>\n
<\/p>\n
Usage:<\/strong> \n\u2022 Specified as “GENERATED AS IDENTITY”. \n\u2022 The default value starts from 1 and increases 1 by 1. \n\u2022 Value can be assigned with START WITH and INCREMENT BY and the increment value can be set. \n\u2022 The IDENTITY column is reset if the table is dropped or re-created.<\/p>\n
<\/p>\n
Example 1:<\/strong> \nIn this example, the value of the column with IDENTITY starts from 1 and increases 1 by 1.<\/p>\n
create table table1\r\n(id number generated as IDENTITY, NAME varchar2(50));\r\n--\r\ninsert into table1 (adsoyad) values ('Ahmet Duru\u00f6z');\r\ninsert into table1 (adsoyad) values ('Kemal Duru');\r\ninsert into table1 (adsoyad) values ('Ceyhun Y\u0131lmaz');\r\n--\r\nselect * from tablo1;\r\nID\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 NAME\r\n---------- ----------\r\n1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0Ahmet Duru\u00f6z\r\n2\u00a0 \u00a0 \u00a0 \u00a0 \u00a0Kemal Duru\r\n3\u00a0 \u00a0 \u00a0 \u00a0 \u00a0Ceyhun Y\u0131lmaz<\/pre>\n
<\/p>\n
Example 2:<\/strong> \nIn this example, the initial value and increment value of the IDENTITY column are specified.<\/p>\n
create table table2\r\n(id number generated as IDENTITY ( start with 10 increment by 11), NAME\r\nvarchar2(10));\r\n--\r\ninsert into table2(isim) values ('Ahmet Duruoz');\r\ninsert into table2(isim) values ('Bekir Kilic');\r\ninsert into table2(isim) values ('Nurullah Cakir');\r\n--\r\nselect * from table2;\r\nID\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 NAME\r\n---------- ----------\r\n10\u00a0 \u00a0 \u00a0 \u00a0 \u00a0Ahmet Duruoz\r\n21\u00a0 \u00a0 \u00a0 \u00a0 \u00a0Bekir Kilic\r\n32\u00a0 \u00a0 \u00a0 \u00a0 \u00a0Nurullah Cakir<\/pre>\n
<\/p>\n
A column with DEFAULT value is SEQUENCE:<\/strong><\/p>\n
In Oracle 12c, we can assign the default value to column in several ways:<\/p>\n
\n
A default value can be assigned when column value is added as NULL.<\/li>\n
The default value of the column is given with a SEQUENCE (.nextval or .currval).<\/li>\n<\/ul>\n
<\/p>\n
Example:<\/strong><\/p>\n
In this example, the default value of the id column is assigned as a sequence.<\/p>\n
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.<\/p>\n
First we create a sequence:<\/p>\n
create sequence default_test_seq start with 1 increment by 1;<\/pre>\n
create table table3\r\n(id number default default_test_seq.nextval not null,\r\nname varchar2(10) ,\r\nsurname varchar2(10) default on null 'unidentified' not null);\r\n\r\ninsert into table3(name,surname) values ('Ahmet',null);\r\ninsert into table3(name) values ('Bekir');\r\ninsert into table3(name,surname) values ('Kerim','Oner');\r\n\r\nselect * from table3;\r\nID\u00a0 \u00a0 \u00a0 \u00a0 \u00a0NAME SURNAME\r\n---------- ---------- ----------\r\n1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Ahmet\u00a0 \u00a0 unidentified\r\n2\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Bekir\u00a0 \u00a0 \u00a0 unidentified\r\n3\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Kerim\u00a0 \u00a0 Oner<\/pre>\n\n