Hello, I have a requirement to reset the auto-increment id field of a table in my database on a certain condition. Right now I am doing it using SQLQuery. Is there a way to do this with HSQL. I want to avoid using SQL because I have already run into SQL issues while migrating from HSQLDB to MySql.

post your SQL query which resets MySql auto increment id

The Following is the query :

ALTER TABLE USER_GROUP_ASSOCIATION AUTO_INCREMENT = 1

Hi,
Most probably auto_increment is used as a primary key, if so, its dangerous to reset it and it may cause primary key contraint error. So I want to know at what situation you would like to reset the auto_increment?

This particular table is re-initialized periodically from an external data source. This table actually maintains a many to many relationship between 2 other tables namely users and groups. The number of records inserted every time could be well over 10K, so if the population happens at a quick frequency, i will be at the risk of hitting the maximum.

Another option i thought of was to delete the table and recreate it at run time using hibernate. If that is easier i could use that option as well.