Small demo about sequences and identity columns

 A recent twitter discussion lead me to (re)test the syntax a little more around auto-incrementing ID columns that work without triggers. I was especially curious about the LIMIT VALUE option, that I didn’t know before.

identity options

This option is documented (as opposed to RESTART which still seems not to made it into the docs)

Demo 1 – Attach a sequence to a table via the DEFAULT option

The comments explain what I’m trying to do/show.

-- Create some sequences to be used later create sequence mySeq1; Sequence MYSEQ1 created.   create sequence mySeq2; Sequence MYSEQ2 created.   -- Table with PK not tied to a sequence create table t1 (id  number not null primary key                , txt varchar2(30)); Table T1 created.   -- fill PK using a Sequence insert into t1 values (mySeq1.nextval, 'Test1'); 1 row inserted.   -- set the sequence to a new start value 55 alter sequence mySeq1 restart start with 55; Sequence MYSEQ1 altered.   -- insert more data insert into t1 values (mySeq1.nextval, 'Test2'); 1 row inserted.   -- check data select * from t1;

ID TXT
1 Test1
55 Test2

Unfortunately we can not easily change our PK into an identity column.

alter table t1 modify id generated as identity;

ORA-30673: column to be modified is not an identity column

alter table t1 modify id generated by default on null as identity;

ORA-30673: column to be modified is not an identity column

Other syntax variants including using LIMIT VALUE also do not work.

However it is easily possible to use a default value for the column and populate it by an existing (user managed) sequence.

-- Use a default value column and a different sequence alter table t1 modify id default on null mySeq2.nextval; Table T1 altered.   -- Trying to insert a values give a UK error, -- because we used a fresh sequence which gave us value 1 -- which exists already. insert into t1 (txt) values ('Test3'); ORA-00001: unique constraint (MYUSER.SYS_C00145531) violated   -- set the sequence to a different value using RESTART alter sequence mySeq2 restart start with 70; Sequence MYSEQ2 altered.   insert into t1 (txt) values ('Test3'); 1 row inserted.   insert into t1 values (null, 'Test4'); 1 row inserted.   insert into t1 values (default, 'Test5'); 1 row inserted.   -- Check data select * from t1;

ID TXT
1 Test1
55 Test2
70 Test3
71 Test4
72 Test5

So Demo 1 shows some commands around normal ID columns that are populated via a default value setting.

Demo 2 – Use identity column from the start

create table t2 (id  number generated always as identity primary key                , txt varchar2(30)); Table T2 created.   -- Try to insert some rows insert into t2 values (null, 'Test1'); ORA-32795: cannot insert into a generated always identity column   insert into t2 values (default, 'Test2'); 1 row inserted.   insert into t2 (txt) values ('Test3'); 1 row inserted.   -- Try to insert a record via plsql declare   r t2%rowtype; begin   r.txt := 'Test4';   insert into t2 values r; end; /

ORA-32795: cannot insert into a generated always identity column

-- hide the ID column then try row insert by record again alter table t2 modify id invisible; Table T2 altered.   declare   r t2%rowtype; begin   r.txt := 'Test5';   insert into t2 values r; end; / PL/SQL procedure successfully completed.   -- Check data select * from t2;

TXT
Test2
Test3
Test5

The ID is missing? Well it is there.

select id, txt from t2;

ID TXT
1 Test2
2 Test3
3 Test5

Hiding the ID column is one possible way to make certain types of inserts work again. Not my preferred way thou.

-- make column visible again alter table t2 modify id visible; Table T2 altered.   -- logical column order now is changed. This can impact DML that depend on column order! select * from t2;

TXT ID
Test2 1
Test3 2
Test5 3

-- restore original column order by toggling other columns invisible alter table t2 modify txt invisible; Table T2 altered.   alter table t2 modify txt visible; Table T2 altered.   -- modify to allow other data values (instead of generated ALWAYS) alter table t2 modify id generated by default on null as identity ; Table T2 altered.   -- Try inserts again that didn't work previously insert into t2 values (null, 'Test1'); 1 row inserted.   declare   r t2%rowtype; begin   r.txt := 'Test4';   insert into t2 values r; end; / PL/SQL procedure successfully completed.   -- Check data select * from t2;

ID TXT
1 Test2
2 Test3
3 Test5
4 Test1
5 Test4

-- add custom IDs insert into t2 values (-1, 'Test6'); 1 row inserted.   insert into t2 values (100, 'Test7'); 1 row inserted.   -- Check data select * from t2;

ID TXT
1 Test2
2 Test3
3 Test5
4 Test1
5 Test4
-1 Test6
100 Test7

So now we can insert other ID values into that column, even thou, we have an identity column. Eventually this will lead to a UK error when the value created by the Oracle managed sequence reaches 100.

This can be solved with the LIMIT VALUE clause

-- Check current high water mark (last value) of sequence identity column select identity_column, data_default from user_tab_columns where table_name = 'T2' and column_name = 'ID';   IDENTITY_COLUMN DATA_DEFAULT ------- YES "MYUSER"."ISEQ$$_258212".nextval   -- using the sequence name from the previous select select last_number, cache_size from user_sequences where sequence_name = 'ISEQ$$_258212';   LAST_NUMBER CACHE_SIZE ------- 24  20   -- reset Identity column to the highest value that is currently in the table alter table t2 modify ID  generated by default on null as identity start with limit value; Table T2 altered.   -- Check high water mark again select last_number, cache_size from user_sequences where sequence_name = 'ISEQ$$_258212';   LAST_NUMBER CACHE_SIZE ------- 101 20   -- insert insert into t2 values (default, 'Test8'); 1 row inserted.   -- Check data select * from t2;

ID TXT
1 Test2
2 Test3
3 Test5
4 Test1
5 Test4
-1 Test6
100 Test7
101 Test8

Success!

So Demo 2 showed commands how to work with an identity column. Including the possibility to switch it from generated always as identity to generated by default on null. Which opens up some ways to manipulate the data inside this managed ID column.

Remember we can not influence (alter) the Oracle managed sequence that drives the identity column directly. But we can change its properties via ALTER TABLE MODIFY .

cleanup code

-- cleanup drop sequence mySeq1; drop sequence mySeq2; drop table t1 purge; drop table t2 purge;