Identity Columns...

In Oracle 12c the new identity column has been added. This is a column type that basically simulates a sequence and trigger to insert an auto generated number to the column and is really quite simple to use. Here is a super simple example:

 create table my_table

   (

   mt_id number generated as identity,
   name varchar2(1000),

   email varchar2(1000)

   );

This creates a 3 column table, first being our identity column and the second a varchar name, and third a varchar email address. Lets insert a few values and see what we get:

 insert into my_table(name, email) values ('Jon Doe', 'jdoe@email.zzz');
 insert into my_table(name, email) values ('Joe Blow', 'jaybee@email.zzz');

 select * from my_table;

     MT_ID NAME                 EMAIL
 ---------- -------------------- --------------------
          1 Jon Doe              jdoe@email.zzz
          2 Joe Blow             jaybee@email.zzz

Great!! Our values were inserted and the identity column was auto populated from the sequence that was made. How cool and easy is that?

We could leave it there, but it does raise a few questions. Is there uniqueness on the identity column? What happens if we insert a value for mt_id?

Looking at the constraints on our new table we see that there is  only a not null check constraint.

 select table_name, constraint_type TYPE, SEARCH_CONDITION_VC CONDITION from      user_constraints where table_name ='MY_TABLE';

 TABLE_NAME           TYPE  CONDITION
 -------------------- ----- --------------------
 MY_TABLE             C     "MT_ID" IS NOT NULL

So that's a little concerning. What happens if we insert id 3 manually:

 insert into my_table(mt_id, name, email) values ('3', 'Art Smith', 'painter@email.zzz');

 ERROR at line 1:
 ORA-32795: cannot insert into a generated always identity column

Ok... that's good, but why did it happen? Well it seems that there are a few options for defining identity columns. When specifying 'generated always' we ensure that only sequence values can be used. If we define with 'generated by default' then only when we do not provide a value will the sequence be used, even if it is a null value. If we define it with 'generated by default on null' Then only not null values will over ride the sequence, nulls will default back to the sequence number.

But wait, we didn't use any of those in our definition. The following query shows us that default seems to be 'generated always' which really forces you to define otherwise if you want to bypass the sequence values:

 SELECT table_name,  column_name,  generation_type FROM   user_tab_identity_cols WHERE    table_name = 'MY_TABLE';

TABLE_NAME    COLUMN_NAME  GENERATION

-------------------- --------------------      ----------

MY_TABLE             MT_ID                ALWAYS

Ok, on to the next questions. What if we set generation to default and insert a value that already exists? What if we insert the next value in the sequence and then call the sequence for that value? First this setup:

drop table my_table;

create table my_table

  (

  mt_id number generated by default as identity,

  name varchar2(1000),

  email varchar2(1000)

  );

  

  

 insert into my_table(name, email) values ('Jon Doe', 'jdoe@email.zzz');

 insert into my_table(name, email) values ('Joe Blow', 'jaybee@email.zzz');

Then the insert of mt_id=2 which belongs to Joe Blow:

 insert into my_table(mt_id, name, email) values ('2', 'Art Smith', 'painter@email.zzz');

 1 row created.

 select * from my_table;

     MT_ID NAME                 EMAIL

 ---------- -------------------- --------------------

         2 Art Smith            painter@email.zzz

         1 Jon Doe              jdoe@email.zzz

         2 Joe Blow             jaybee@email.zzz

Well, it looks like uniqueness is not maintained in that situation. Lets try the other way around:

   insert into my_table(mt_id, name, email) values ('3', 'Guy Homme', 'guy@email.zzz');

1 row created.

 select * from my_table;

     MT_ID NAME                 EMAIL

---------- -------------------- --------------------

         2 Art Smith            painter@email.zzz

         3 Guy Homme            guy@email.zzz

         1 Jon Doe              jdoe@email.zzz

         2 Joe Blow             jaybee@email.zzz

 insert into my_table( name, email) values ( 'Scott Tiger', 'orclguy@email.zzz');

1 row created.

 select * from my_table;

     MT_ID NAME                 EMAIL

---------- -------------------- --------------------

         2 Art Smith            painter@email.zzz

         3 Guy Homme            guy@email.zzz

         3 Scott Tiger          orclguy@email.zzz

         1 Jon Doe              jdoe@email.zzz

         2 Joe Blow             jaybee@email.zzz

No uniqueness maintained there either. Is this bad? No, not really. I would say that is working as intended, though I'm not sure I would use and Identity in such manner. I'm happy the default is to always generate and thus enforce the uniqueness. 

Now you may be asking, 'What is stopping me for setting the value of the sequence back to a value that was previously used?' 

Well, it seems that this is a system generated sequence and once created you can not alter it, even as sys. 'ORA-32793: cannot alter a system-generated sequence'