Auto increment ID - deleting from table - new entries start after deleted number!

Databases
1

Hi,

I have a problem with the MySQL database.

Let me explain: If this was the table with ID, name, price:

1, apple iphone 1, £50
2, apple iphone 2, £100
3, apple iphone 3, £200
4, apple iphone 4, £400

If I delete row 4 the table will read:

1, apple iphone 1, £50
2, apple iphone 2, £100
3, apple iphone 3, £200

Now if i then add the iphone 5 the table looks like this:

1, apple iphone 1, £50
2, apple iphone 2, £100
3, apple iphone 3, £200
5, apple iphone 5, £1000

Since I have deleted row 4 with ID 4, I expect new entries to begin with ID number 4. It does not make sense to start from ID 5!

The only way I know how to start from 4 again is the manually enter ID number 4.

Is it possible to have the ID start from the last one in the table?!?

Matt.

2

Actually it does. It’s a safety check against non-cascading deletions colliding on ID.

(And that probably sailed over your head. So lets give a simplistic example. This is NOT an example of how a database should be coded, but it happens, so mySQL tries to shield people a bit :P)

You have products:

ID Name
1 Apple
2 Orange

and Sales:
ItemID PersonID
1 2
1 1
2 1

Now, you decide to stop selling Oranges. So (and i know this SHOULD be done with an active/deleted flag field, but this is for simplicity) you decide to delete Oranges from your product table. Okay.

Now you add bananas. If, as you want, the system assigns ID 2 to Bananas, what items did Person 1 purchase? Your database will say an Apple and a Banana. But they bought an Orange, not a Banana.

(And yes, I know that there would be an issue with it saying the person purchased an Apple and <I dont know>. Remember when I said this example was not how it should be coded?)

3

no, you don’t

the purpose of an auto_increment number is to be unique, not necessarily consecutive

so you don’t have a problem

:slight_smile:

4

No it’s not possible.