CODEWITHSUNDEEP
phpmysqlsqlmysqli
Batzz
Batzz

Reputation: 432

SQL Primary Key reset?

I have a database with a primary key and whenever I add a row it remembers the primary key from before even though ive deleted the rows, heres some images to explain better what I want to fix, if possible :
1.https://i.sstatic.net/9z2xZ.png
2.https://i.sstatic.net/Px5pt.png
You see below ive deleted the row that contains TEST ( the only row in the table ) then added 2 new ones. The new ones continue with the primary key even though the row is no longer there , is there any way to make it forget it or reset it so whenever database is run the ID's get organized in 1-2-3 etc.

Upvotes: 2

Views: 1832

Answers (3)

Steve
Steve

Reputation: 3703

This doesn't have anything to do with a Primary Key. What you're referring to is an auto increment. A Primary Key doesn't have to be in integer, or even an automatic value (auto increment) - it is possoble although not as common to use other data types as a primary key.

The auto increment is designed to increment and it will never use previous keys that may be been deleted.

It's usually not a good idea to reset it, however you can by either dropping and recreating the table or using

ALTER TABLE tablename AUTO_INCREMENT = 1

If you wanted to maintain a separate column without holes you can do this by using a trigger.

Upvotes: 2

LukasS
LukasS

Reputation: 176

If it's MyISAM table, you can reset primary_key with this ALTER TABLE table_name AUTO_INCREMENT = 1;. If the table holds some rows it will set up next number (after last id in that table) for that primary_key.

You can add to your INSERT statements this part at the end ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID)

Upvotes: 0

czifro
czifro

Reputation: 784

You will need to drop and create the table to reset primary key back to 1, or 0 depending on the DBMS.

Upvotes: 0

Related Questions