reset autoincrement primary key sql, mysql [duplicate]

Question

I have this table Post with 1000 rows, where id_post is primary key Set to autoincrement

OLD

id_post | post
  1        hi1
  2        hi2
   ...
 1000     hi1000

I deleted rows from 301 to 1000. So I have now a list of rows from 1 to 300.

NEW

id_post | post
  1        hi1
  2        hi2
   ...
 300     hi300

PROBLEM

when trying to add a new row, id_post starts at 1001, how do I reset the table and make it to start from 301?

And after exhausting 700 IDs in gaps what will you do? AUTOINCREMENT should be left as it is. You cannot guarantee no gaps because it was not designed that way. ALTER TABLE post AUTO_INCREMENT = 300; — Lukasz Szozda
– Lukasz Szozda, Commented Jun 21, 2018 at 13:32
I know it should be left. But this is a posts table, where there are default posts and users posts. the 300 are default posts. — Dan
– Dan, Commented Jun 21, 2018 at 13:34
1) create new table from 1 to 1000 2) delete the old table 3) alter the name of the new table to the name of the old one — Super Mario
– Super Mario, Commented Jun 21, 2018 at 13:39
@LukaszSzozda approach is the way to go. thank you. Lukasz feel free to post an answer to mark it. thank you it worked. — Dan
– Dan, Commented Jun 21, 2018 at 13:41
@Dan - you can also set the id to 1 (like answer from Lukasz Szozda) Mysql will then get the next free id. — Bernd Buffen
– Bernd Buffen, Commented Jun 21, 2018 at 13:47

Lukasz Szozda · Accepted Answer · 2018-06-21 13:43:47Z

1

First of all I would leave gap as it is. If you really want to change it you could use:

 ALTER TABLE post AUTO_INCREMENT = 300;
 -- around 700 insterts
 ALTER TABLE post AUTO_INCREMENT = ?; -- where ? is max value of id_post

answered Jun 21, 2018 at 13:43

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nedzad G · Accepted Answer · 2018-06-21 13:41:41Z

0

You can truncate that table in order to reset your identity column. Like this:

truncate table [table_name]

Or you can use CHECKIDENT command:

  DBCC CHECKIDENT('table', RESEED, 0)

answered Jun 21, 2018 at 13:41

Nedzad G

1,0371 gold badge10 silver badges21 bronze badges