1

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.

Tim Frey
  • 9,901
  • 9
  • 44
  • 60

2 Answers2

1

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.

cweiske
  • 30,033
  • 14
  • 133
  • 194
0

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
Tim Frey
  • 9,901
  • 9
  • 44
  • 60