66

In my table tbphotos I had a 100 records. I then deleted all the records and now that I want to restart data entry I see that my primary key doesn't start from 1, but it starts from 101,

Is there any way to reset the primary key?

I am using MySQL administrator account.

Script47
  • 14,230
  • 4
  • 45
  • 66
Kaveh
  • 2,530
  • 7
  • 29
  • 34

5 Answers5

110

alter table foo AUTO_INCREMENT = 1

Donnie
  • 45,732
  • 10
  • 64
  • 86
36

You can reset the auto-increment like this:

ALTER TABLE tablename AUTO_INCREMENT = 1

But if you are relying on the autoincrement values, your program is very fragile. If you need to assign consecutive numbers to your records for your program to work you should create a separate column for that, and not use a database auto-increment ID for this purpose.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 11
    Can you explain why using auto increment values makes your program fragile. – Ben Dec 09 '13 at 16:03
  • @MarkByers any chance we could get an answer to the above comment. – Script47 Apr 05 '18 at 16:17
  • 10
    Most of the times you use an ID in your URL, let's say yourapp.com/user/1. Since your ID is auto incremented someone can assume that user with ID 2 might also exist. I alter the URL to /user/2 and that might give unexpected behavior if you didn't defend your app against this. This also gives you information about your app, lets say user/1000 exists this means that you at least have a 1000 registered users. Same for items, sales, orders etc. I could analyse your app and create statistics without you knowing about it, you dont want to 'leak' this information thats why you use random IDs. – Skoempie May 23 '18 at 07:23
13

The code below is best if you have some data in the database already but want to reset the ID from 1 without deleting the data. Copy and run in SQL command

ALTER TABLE members DROP ID;
ALTER TABLE members AUTO_INCREMENT = 1;
ALTER TABLE members ADD ID int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
icynets
  • 337
  • 2
  • 5
5

If you use TRUNC instead of manually deleting records, your primary key will be reset.

Vladimir Kocjancic
  • 1,814
  • 3
  • 22
  • 34
  • TRUNCVATE does not reset the auto_id on INNODB tables – Slashterix Nov 27 '09 at 17:52
  • 2
    @Slashterix - The docs specify otherwise: http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html... Edit: I just realized how old this answer and comment are. – Parris Varney Dec 04 '15 at 13:57
  • 2
    @ParrisVarney Thanks, you are correct. I was confusing the behaviour described here http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization – Slashterix Dec 05 '15 at 00:21
2

This is the best script for reset auto increment:

ALTER TABLE foo MODIFY your column increment int (11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ramadhan ibrahim
  • 52
  • 3