Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 3 February 2014

Identity Columns Feature in Oracle 12c

Before Oracle 12c there was no direct equivalent of the Auto Number or Identity functionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type.
The Syntax:
GENERATED
[ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ (identity_options ) ]

Using 'ALWAYS' keyword will force the use of the identity. In that case if an insert statement references
the identity column, even you specify a NULL value, an error is occured.
CREATE TABLE payslip_master (
  empid NUMBER GENERATED ALWAYS AS IDENTITY,  latin_name Varchar2(50));

Insert into payslip_master (latin_name) values('SADHAN_001');
Insert into payslip_master (latin_name) values('SADHAN_002');
SQL> select * from cust_master;
     EMPID LATIN_NAME
---------- --------------------
         1 SADHAN_001
         2 SADHAN_002
SQL> Insert into cust_master (empid,latin_name) values(101,'ISSCO_003');
Insert into payslip_master (empid,latin_name) values(101,'ISSCO_003')
               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement,
but if the column is referenced, the specified value will be used in place of the identity. In that case if you try
to specify the value NULL then an error occured, since identity columns are always NOT NULL.

CREATE TABLE payslip_master(
  empid NUMBER GENERATED BY DEFAULT AS IDENTITY, latin_name Varchar2(50));

Insert into payslip_master (latin_name) values('SADHAN_001');
Insert into payslip_master (empid,latin_name) values(40,'SADHAN_002');
SQL> select * from payslip_master;
     EMPID LATIN_NAME
---------- --------------------
         1 SADHAN_001
        40 SADHAN_002
Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified.
CREATE TABLE payslip_master(
EMPID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  latin_name varchar2(50));

Insert into payslip_master (empid,latin_name) values(null,'SADHAN_001');
Insert into payslip_master (empid,latin_name) values(40,'SADHAN_002');
Insert into payslip_master (latin_name) values('SADHAN_003');
SQL> select * from payslip_master;
     EMPID LATIN_NAME
---------- --------------------
         1 sadhan_001
        40 sadhan_002
         2 sadhan_003
As we know oracle uses a sequence internally to generate value to populate the identity column.

0 comments:

Post a Comment