how to reset auto increment id after deleting a row in SQLdatatbase in android studio

Question

how to reset auto increment id after deleting a row in SQLdatatbase in android studio

Answer 1

@John, I do not have Android Studio, but if there's a SQL query window where you can execute commands maybe you can try the steps below.

If you truncate your table you'll automatically reset auto increment id, that's the easiest way, for updating identity (auto increment field) while there's still records in the table is tricky, and risky (read: not recommended), you may suffer from identity number conflict or reference integrity fault, if you're not being enough careful.

[To truncate a table]
TRUNCATE TABLE <table_name>

To modify the next identity value:

[For MySQL]
ALTER TABLE <table_name> AUTO_INCREMENT=<new_value>

[For MS SQL Server 2008]
DBCC CHECKIDENT ("table_name", RESEED,1);
*Next insert will have id(1)

[For MS SQL Server 2012 or 2008 r2]
DBCC CHECKIDENT ("table_name", RESEED,0);
*Next insert will have id (1)

For some funny reason, Microsoft is being inconsistent with their own product, as you can see. I don't know how to do it in Oracle, nor whether if it was possible.

Final note, test it on an experimental table first, save yourself the hassle in case you do wrong.

Hth, cmiiw