Problem with Auto_Increment Fields

run2web

Is there a way to make an auto_increment field to revert back to the next number after a file has been deleted?

Explaination:

I have an ID # that is auto_incremented. While testing we have added a few extra people whose names are bogus for testing purposes. When we delete them the auto_increment number remembers the last number used and doesn't reset itself to the current "next" number. The numbering then goes as follows:

1
2
3
7
8
9
20

Any ideas how I make it not do this? If the user deletes an ID then the whole numbering system will be off as well. 😕

Lars_Berg

Auto_increment is not intended to be a numbering system. It's only purpose is to provide unique values. So, in general, the answer is no.

Why do you need consecutive numbers?

run2web

Originally posted by Lars Berg
Why do you need consecutive numbers?

I mostly am using the numbering for tracking purposes in the database. The person inserting data does not see the number except on the transaction page. It will also be used for searching. Perhaps it is not that big of a deal but I wanted the numbers consecutive.

dannys

No, the autonumber system was designed not to use numbers it's already used.

Imagine if you have a table of articles - you select them using the id (an autonumber), ok?

Now an article is deleted.

If a new article is written and it used the id of the deleted article then people still linking to the old article would be served a valid (but most likely completely irrelevant) article, instead of the 'article is dead' message and there'd be nothing anyone could do to rectify it.

So, autonumbers are merely there to provide a unique field used for indexing - it's not supposed to look pretty.

If you need consecutive auto generated numbers then have another field, say articleNo - a regular integer. When you insert data into the table, your query would look something like

INSERT INTO articles('id','title','content','articleNo') VALUES ('','My Article','This is my article',MAX(articleNo)+1);

run2web

Ok, that makes sense. At least now I can accept the reasoning behind it. I will see if I can work the suggested code into the db without too many problems.

Thank you.

el_kab0ng

why not just auto generate a random number for tracking purposes instead of using the autonumber feature?

run2web

I may do that. That would certainly work for my purposes

Thanks for the idea 🆒

el_kab0ng

before you ask...😃

$id = rand(1,100000);