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

Question

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.

Answer 1

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

Answer 2

hi this is great article