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.


Even if it's not a direct answer to the question: With MySQL, you can do a

ALTER TABLE my_little_table AUTO_INCREMENT =2000

to set the auto increment value.


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:


protected by Will Nov 19 '10 at 14:04


Would you like to answer one of these unanswered questions instead?
