CODEWITHSUNDEEP
mysqlprimary-key
Kaveh
Kaveh

Reputation: 2650

How to reset the primary key of a table?

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.

Upvotes: 69

Views: 104466

Answers (5)

ramadhan ibrahim
ramadhan ibrahim

Reputation: 52

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;

Upvotes: 2

IcyNets
IcyNets

Reputation: 356

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;

Upvotes: 14

Mark Byers
Mark Byers

Reputation: 838146

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.

Upvotes: 42

Vladimir Kocjancic
Vladimir Kocjancic

Reputation: 1844

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

Upvotes: 5

Donnie
Donnie

Reputation: 46913

alter table foo AUTO_INCREMENT = 1

Upvotes: 114

Related Questions