3

i'm new to the whole database scenario and just learning. I have a database mysql and i am using phpmyadmin to edit fields etc. I created a auto_incremenet 'id' field that is set as a primary. If i add 5 fields, the id will be '1,2,3,4,5'. If i delete all those fields and add another 5 fields, why does the ID go to '6,7,8,9,10'? (instead of going back to 0, since technically the other fields do not exist anymore so its unique right?). Is there a way to make the id be sequentially numeric? 1,2,3,4,5 ?

Spike Lee
  • 411
  • 2
  • 11
  • 23
  • 2
    Primary keys can only ever be used once... thus even if a record is deleted the PK is not reallocated to a new record. – Ben Everard Aug 01 '11 at 08:39
  • If you do need to track something like "InUseID" you will want a seperate field for it and to make it clear its not the same as the primary key "ID" – Matt Aug 01 '11 at 08:51

5 Answers5

5

You don't need to worry about it. Your primary key should be an unsigned int which can hold large enough numbers.

Luchian Grigore
  • 253,575
  • 64
  • 457
  • 625
1

I believe this happens because when using a relational database system, you can "link" rows to other rows, usually by their id. If you start reusing IDs, then you might end up in confusing situations.

You can reset the auto_increment counter to whatever you wish using the following query:

ALTER TABLE tableName AUTO_INCREMENT=123

If you are deleting all rows in your table, you can use TRUNCATE which will also reset the counter.

TRUNCATE TABLE tableName
Neil
  • 726
  • 10
  • 14
1

I think the main reason for this behaviour is efficiency, it means that MySQL doesn't need to track which numbers are not used, it only need to know which number was last used.

Jaydee
  • 4,138
  • 1
  • 19
  • 20
0

Have a look at this question. But basically:

ALTER TABLE mytable AUTO_INCREMENT = 1;

UPDATE

As mentioned, fiddling with auto generated PKs will indefinitely cause you a lot of headache. Or writing a bunch of boilerplate code to do housekeeping.

Community
  • 1
  • 1
Nico Huysamen
  • 10,217
  • 9
  • 62
  • 88
  • Note however, that fiddling with autogenerated PK values in production environment can lead to very frustrating results. – Mchl Aug 01 '11 at 08:42
0

You will have to reset the auto increment, so you can renumber the auto_incremented values, read more about the solutions here http://www.dbuggr.com/milly/reset-auto-increment-mysql/.

Risto Novik
  • 8,199
  • 9
  • 50
  • 66