Oracle 12c IDENTITY Columns & Default SEQUENCES

I will try to explain the identity field, which is my favorite innovation in Oracle 12c.

MS SQL, MYSQL users are familiar with the auto-incrementing field, which is now available in Oracle databases. Previously, SEQUENCE was created, the new value of this SEQUENCE was taken during the table registration and the operation was performed. Triggers could also be created for this. Oracle 12c offers 2 options for this operation: IDENTITY column and SEQUENCE to be used as the default value of the column.

IDENTITY Column

The IDENTITY column is new to Oracle, but has been a feature in other databases for years. IDENTITY columns actually create a SEQUENCE in the background. But this creation is done automatically. When the table is dropped or recreated, the IDENTITY value starts over again.

Oracle databases have had SEQUENCE for years. The IDENTITY column allows a SEQUENCE to be used as part of the column definition.

Usage:
•It is specified as “GENERATED AS IDENTITY”.
•It starts from 1 by default and increases by 1.
•Values ​​can be assigned and increments can be determined with START WITH and INCREMENT BY.
•If the table is dropped or recreated, the IDENTITY column is reset.

Example 1:
In this example, the value of the column with IDENTITY can start from 1 and be increased by one.

create table table1
(id number generated as IDENTITY, name surname varchar2(50));
--
insert into table1 (namesurname) values ​​('Ahmet Duruöz');
insert into table1 (namesurname) values ​​('Kemal Duru');
insert into table1 (namesurname) values ​​('Ceyhun Yılmaz');
--
select * from table1;
ID NAME SURNAME
---------- ----------
1 Ahmet Duruoz
2 Kemal Duru
3 Ceyhun Yilmaz

Example 2:
In this example, the initial value and increment value of the IDENTITY column are specified.

create table table2
(id number generated as IDENTITY ( start with 10 increment by 11), name
varchar2(10));
--
insert into table2 (name) values ​​('Ahmet');
insert into table2 (name) values ​​('Bekir');
insert into table2 (name) values ​​('Cemal');
--
select * from table2;
ID NAME
---------- ----------
10 Ahmed
21 Bekir
32 Jamal

Column with DEFAULT value of SEQUENCE:

Oracle 12c has improved column properties and provided several methods for assigning default values:

• When the column value is added as NULL, a default value can be assigned.
• The default value of the column can be given with a SEQUENCE (.nextval or .currval).

Example:
In this example, the default value of the id column is given as a sequence, and the value of the surname column is given as ‘UNDEFINED’ when it is NULL. In this way, there is no need for a trigger to automatically assign a value to the column.

First we create the sequence:

create sequence default_test_seq start with 1 increment by 1;
--
create table table3
(id number default default_test_seq.nextval not null,
name varchar2(10) ,
surname varchar2(10) default on null 'UNDEFINED' not null);
--
insert into table3 (name,surname) values ​​('Ahmet',null);
insert into table3 (name) values ​​('Bekir');
insert into table3(name,surname) values('Kerim','Öner');
--
select * from table3;
ID NAME SURNAME
---------- ---------- ----------
1 Ahmet UNDEFINED
2 Bekir UNDEFINED
3 Kerim Suggestion