Search code examples
auto-incrementjavadb

JavaDB: Is it possible to change auto-increment offset on existing table?

Is it possible to change the auto-increment offset on a pre-existing table with JavaDB?

I'm having a problem where inserting new records usually (but not always) fails with an error complaining about using an existing key (my auto-increment column). To populate this database, I took a dump from another database (MySQL) and used a JavaDB stored procedure to insert them all into the corresponding JavaDB table. My theory is that inserting these records copied the existing IDs from the MySQL table. Now the auto-increment functionality is dishing out existing IDs. I figure explicitly setting the offset to some high number will allow the auto-increment to work again.

Solution

  • I don't know how to directly change the offset, but I managed to fix this by:

    1. Changing the increment amount by X (1 million in my case).
    2. Inserting a dummy record.
    3. Reducing the increment amount back down to 1.
    4. Deleting the dummy record.

    I used this SQL statement to change the increment amount:

    ALTER TABLE tbl ALTER COLUMN col SET INCREMENT BY x