Tuesday, April 12, 2011

Start all your Oracle sequences from 1000

Say you have autogenerated IDs, using sequences.
If you start using your application, the first entity will have ID=1.

If you have already inserted "test" data, from SQL Insert, using that same ID=1,
this can generate a UNIQUE constrain violation.

So as a rule just create the sequences as

create sequence BLA_SEQ MINVALUE 1000 START WITH 1000 INCREMENT BY 1;

Funnily, you cannot CHANGE the "START WITH" using a ALTER SEQUENCE, otherwise you get this funny error:

ORA-02283: cannot alter starting sequence number
Cause: Self-evident.
Action: Don't alter it.

Self Evident??? You lazy self-referential B....

No comments: