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'
