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.
"so there is some metadata about the relationship"
ReplyDeleteThat 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.
I think it would also be good to provide a sequence as a DEFAULT value for a column.
ReplyDelete@Scott Wesley: This feature will be available in Oracle 12c ...
ReplyDeleteCDB1> 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.