Resetting Auto Increment

Hibernate, Java, Sql 1 Minute

This method let you purge a table completely and also let the primary key start with one or from whatever digit you want.

We all have gone through the pain of not getting the primary key back to one once the data (rows) from the table is deleted. Deleting all the rows from atable wont set a primary key back to one … sadly. But below is the demonstration of setting Auto Increment to any value.

Table before deleting the rows:

name Kathryn Newton Elle Fanning Tom Holland

Now lets delete all the rows from a table. And run the below program again for inserting some data.

Ile public static void main (String args) { // create session factory SessionFactot"y factory = new . buildSessionFactoy(); // create session Session session = factory.getCurrentSession(); try // create an object System . out. println ("Creating new object..." ) firstObject = new Newton"); secondObect = new Fanning"); thirdObject = new Holland"); // start a transmission session . begin Transaction ; // save the student object session . save(firstobject) ; session . save (secondObect) ; session . save(thirdobject) ; // commit transaction session . getTransaction . commit() ; System has been saved successfully" • catch (Exception error occured. Cannot save the object ! !

Table after running the program again:

Tom Holland Kathryn Newton

Here we can see  primary key starts with 4 as before there were 3 entries within the table.

So we need to set the Auto Increment value back to 1 or whatever you wish it to be.

Use below sql queries for that:

  • ALTER TABLE  dbname.tablename AUTO_INCREMENT=1
  • TRUNCATE dbname.tablename (This will delete all the data within a table while resetting auto increment sequence back to one)
test_subject x tes t _s object student Limit to 1000 rows tracker test_subject

Now again delete the data from a table and re-run the program to insert some data into a table.

Table after Altering AUTO_INCREMENT back to one:

Tom Holland Kathryn Newton

Also setting AUTO_INCREMENT can be useful in a scenario where you require a primary key to start from some other digit but not one. Lets say a restaurant id or a hotel Id.

NOTE: TRUNCATE method does not require you to delete the table manually. It will delete the rows itself as soon as you will run the query.

That’s it. Enjoy !!!

Published by adarsh kavtiyal

Published

Leave a comment