#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    England
    Posts
    471
    Rep Power
    29
    Hello,

    I am new to sql so you will have to forgive my questions. I have used MS ACCESS quite allot. When I create 3 records and delete 1 record then add another one the numbering goes out of place.

    example:

    I create 3 records with the id 1,2,3 I delete record 3, then in the future add another record, it then goes 1,2,4.

    Is there anyway in MYSQL to make the numbering go in sequence, if I delete records?.

    Thank you for your time.

    ------------------
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I assume you are talking about auto_increment? If so, what you describe shouldn't happen. If you delete the record with the highest value then the next record inserted should have that value. However, if you delete other than the highest value it won't be replaced. You could reorder with:

    delete from table_name where id=$id;
    update table_name set id=id-1 where id>$id;

    However, that is a bad idea usually. If you have dependencies in other tables based on that id you will create anomolies.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    England
    Posts
    471
    Rep Power
    29
    >However, that is a bad idea usually. If you >have dependencies in other tables based on >that id you will create anomolies.

    Thats true I never thought of that!, thank you.

Similar Threads

  1. Problem Next/Prev record
    By wmdrumaizi in forum PHP Development
    Replies: 2
    Last Post: May 22nd, 2003, 02:06 AM
  2. Loop through record problem
    By paCkeTroUTer in forum PHP Development
    Replies: 5
    Last Post: May 6th, 2003, 11:24 AM
  3. Weird problem: disappearing records
    By Zeus[BTY] in forum MySQL Help
    Replies: 5
    Last Post: June 28th, 2002, 04:37 PM
  4. Wierd Mail problem.. Urgent help required...
    By Jollyguy in forum PHP Development
    Replies: 2
    Last Post: May 2nd, 2002, 12:37 PM
  5. Record from DB Problem
    By jeffcool in forum PHP Development
    Replies: 3
    Last Post: June 13th, 2001, 12:56 PM

IMN logo majestic logo threadwatch logo seochat tools logo