reset autoincrement primary key sql, mysql

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, Jun 21 '18 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, Jun 21 '18 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, Jun 21 '18 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, Jun 21 '18 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, Jun 21 '18 at 13:47

score 1 · Accepted Answer · answered Jun 21 '18 at 13:43

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 '18 at 13:43

Lukasz Szozda

score 0 · Answer 2 · answered Jun 21 '18 at 13:41

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 '18 at 13:41

Nedzad G

2 Answers2