Identity column in oracle 12c Database

Identity column in oracle 12c Database
-----------------------------------------------

Now in Oracle Database 12c Release 1 database has ANSI-compliant IDENTITY column
like Microsoft SQL Server's Identity column for auto increment number generation.

So, now migration from Microsoft SQL Server's to oracle database will be easier
in case of IDENTITY COLUMN.


There are three option for identity  when we will create a table.

1)  by default : BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses
                 the sequence generator to assign a value to the column by default,
                 but you can also explicitly assign a specified value to the column.

2)  by default on null : If you specify ON NULL, then Oracle Database uses the
                         sequence generator to assign a value to the column when a subsequent
                         INSERT statement attempts to assign a value that evaluates to NULL.

3)  always  : ALWAYS If you specify ALWAYS, then Oracle Database always uses the
              sequence generator to assign a value to the column. If you attempt to
              explicitly assign a value to the column using INSERT or UPDATE,
              then an error will be returned. This is the default.


In details explanation is below
=================================


[oracle@halim12c] $ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 18:32:33 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL> col banner format a78
SQL>
SQL>
SQL> select * from v$version ;

BANNER                                                                             CON_ID
------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production            0
PL/SQL Release 12.1.0.1.0 - Production                                                  0
CORE    12.1.0.1.0      Production                                                              0
TNS for Linux: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                  0

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select host_name from v$instance ;

HOST_NAME
----------------------------------------------------------------
halim-srv.localdomain

SQL>
SQL> select name from V$database ;

NAME
---------
HALIM12C

SQL>
SQL>
SQL>
               =============================================================
                       Identity column "generated by default" example
                ===========================================================
              =================================================================
SQL>
SQL>
SQL>
SQL> create table t_identity_def (id number generated by default as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim');

1 row created.

SQL>
SQL> insert into t_identity_def (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def ;

Table truncated.

SQL>
SQL>
SQL> select * from t_identity_def ;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         3 halim2

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert into t_identity_def (id,name) values (null,'halim');
insert into t_identity_def (id,name) values (null,'halim')
                                             *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."T_IDENTITY_DEF"."ID")

SQL>
SQL>


               =============================================================
                  Identity column "generated by default on null" example
                ===========================================================
              =================================================================


SQL>
SQL> create table t_identity_def_null (id number generated by default on null as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL>
SQL> select * from t_identity_def_null ;

no rows selected

SQL>
SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim');

1 row created.

SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (66,'halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1
         4 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def_null ;

Table truncated.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

        ID NAME
---------- ------------------------------
         5 halim1
         6 halim1
         7 halim2

SQL>
SQL>
SQL>

                =============================================================
                       Identity column "generated always" example
                ===========================================================
              =================================================================

SQL>
SQL>
SQL>
SQL> create table t_identity_always (id number generated always as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_always (name) values ('always');

1 row created.

SQL>
SQL> insert into t_identity_always (name) values ('always1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_always;

        ID NAME
---------- ------------------------------
         1 always
         2 always1

SQL>
SQL>
SQL> insert into t_identity_always (id,name) values (3,'always1');
insert into t_identity_always (id,name) values (3,'always1')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL>
SQL>
SQL>
SQL>