SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    No it's not possible.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    I have a problem with the MySQL database.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    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!
    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?)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •