Wednesday, November 16, 2011

Wish List III - The Sequence Cometh

This is another of those 'chores' for which every DBA and his dog has a script.

Copy a new version of a table from Test to Dev and then you have to recreate or adjust the associated sequence. My proposed syntax would be

ALTER SEQUENCE blah_seq SYNCHRONIZE WITH blah_table (blah_column);

That would lock the table, get the highest value of 'blah_column' and reset the 'last_number' of the sequence to that value. Even better, retain the fact that the sequence has been synchronized with that table/column so there is some metadata about the relationship.


Noons said...

"so there is some metadata about the relationship"
That is indeed a very good point!

Most of the time we do not know in which table(s) any given sequence is used...

MSSQL has one over Oracle in there, as you associate a PK of a table with a sequence-like increase. In a nutshell, an internalised trigger that sets the PK to the next unique value upon an insert. No chance of using the wrong sequence.

Scott Wesley said...

I think it would also be good to provide a sequence as a DEFAULT value for a column.

SYSDBA said...

@Scott Wesley: This feature will be available in Oracle 12c ...

CDB1> create sequence s;
Sequence created.
CDB1> create table t
2 ( x int default s.nextval primary key,
3 y varchar2(30)
4 )
5 /
Table created.