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 in