IDENTITY type Column Values

Posted in Oracle 12c, Oracle12cR1, Uncategorized by

Its been really a long time since my last post, I have previously written about IDENTITY type columns here trunc and delete showing how they behave differently. In this post i will be write some more points about IDENTITY type columns. First up as we know IDENTITY type was introduced in Oracle 12c to create auto generated numbers for a column, like we have requirement that a column should be unique than that can be accomplished with this. Previous to 12c we have to use Sequences or Triggers for such requirements. But here i am just looking into IDENTITY type only. So lets get started first simple table:


drop table t1;

create table t1(
n1 number generated always as identity
, v1 varchar(10)
, t1 timestamp
);

insert into t1 (v1, t1) values ('first', systimestamp);
insert into t1 (v1, t1) values ('second', systimestamp);
insert into t1 (v1, t1) values ('third', systimestamp);
insert into t1 (v1, t1) values ('fourth', systimestamp);

select * from t1;

Output of above is


Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
N1 V1 T1
---------- ---------- ---------------------------------------------------------------------------
1 first 24-AUG-16 06.06.40.474764 AM
2 second 24-AUG-16 06.06.40.492531 AM
3 third 24-AUG-16 06.06.40.492723 AM
4 fourth 24-AUG-16 06.06.40.493912 AM

So from the above we can see that for our column n1 Oracle has automatically generated a number starting with 1 and incremented it by 1. Now what if we run the below insert against the above table?


CDB$ROOT@ORCL> insert into t1 (n1, v1, t1) values (5, 'fifth', systimestamp);
insert into t1 (n1, v1, t1) values (5, 'fifth', systimestamp)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

This has given us an error, which is quite self explanatory that we can not insert values explicitly which are defined as “generated always”. However we have one more option to specify for IDENTITY type columns, that is by adding BY DEFAULT, in this case whenever we didn’t specify the value for the column it will generated automatically however we can also add/insert values explicitly. Lets see


drop table t1;

create table t1(
n1 number generated by default as identity
, v1 varchar(10)
, t1 timestamp
);

insert into t1 (v1, t1) values ('first', systimestamp);
insert into t1 (v1, t1) values ('second', systimestamp);
insert into t1 (v1, t1) values ('third', systimestamp);
insert into t1 (v1, t1) values ('fourth', systimestamp);

insert into t1 (n1, v1, t1) values (5, 'explicit', systimestamp);

select * from t1;

Output of the above


Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
N1 V1 T1
---------- ---------- ---------------------------------------------------------------------------
1 first 24-AUG-16 06.19.41.378222 AM
2 second 24-AUG-16 06.19.41.385655 AM
3 third 24-AUG-16 06.19.41.386299 AM
4 fourth 24-AUG-16 06.19.41.387424 AM
5 explicit 24-AUG-16 06.19.41.388295 AM

Now here we can see that Oracle has allowed us to insert the explicit value into the column defined as IDENTITY. Now at below level this IDENTITY type is internally working as a sequence, as we can see from below


CDB$ROOT@ORCL> select object_name, object_type from user_objects uo
where 1=1
and created > to_date('24-Aug-2016 07.00.00', 'DD-Mon-RRRR HH24.MI.SS')
; 2 3 4

OBJECT_NAME OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_99263 SEQUENCE
T1 TABLE

 

HTH

Cheers!!!

Jagdeep Sangwan




		
	
			

			
						


	



				
	
			

	


	
	
	
		

		
Leave a comment