2

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.stack.imgur.com/9z2xZ.png
2.https://i.stack.imgur.com/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.

Batzz
  • 422
  • 4
  • 12
  • 1. have you googled this? 2. what kind of database are you using? – Gelunox Nov 16 '14 at 20:22
  • possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) – Fas M Nov 16 '14 at 20:48

3 Answers3

2

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.

Steve
  • 3,673
  • 1
  • 19
  • 24
0

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

czifro
  • 784
  • 7
  • 24
0

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)

LukasS
  • 176
  • 7
  • I fail to see how `ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID)` is helpful for this question. This will just return the last auto increment id or an existing auto increment id if an update was triggered if it already existed. How would you propose the OP uses this to fill holes by deleted rows? – Steve Nov 16 '14 at 20:38
  • He didn't specify if he adds them and they duplicate, so this code is an addition to the primary_key reset. If duplicate row occurs this will help him keep primary_key in check. This code doesn't return anything just keeps the primary_key without increasing after failed insert. – LukasS Nov 16 '14 at 20:44