Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    WA
    WA is offline
    Administrator
    Join Date
    Mar 2002
    Posts
    2,596
    Thanks
    2
    Thanked 19 Times in 18 Posts

    Refresh row positions after deleting one?

    Let's say I have a table with an "ID" field that's set to auto increment. By default if I delete a row, lets say with ID=3, the table now has a gap in terms of the ID column:

    +--------+-------------+---+
    | name | place | id |
    +--------+-------------+----+
    | kelly | market | 1 |
    | albert | home | 2 |
    | kelly | arena | 4 |
    | kelly | arena | 5 |
    +--------+--------------+---+

    I was hoping there's a way to easily refresh the table so it's 1, 2, 3, 4 again, automatically filling in the gap where "id=3" used to be with the remaining rows.

    Thanks,
    - George
    - JavaScript Kit- JavaScript tutorials and 400+ scripts!
    - JavaScript Reference- JavaScript reference you can relate to.

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    ALTER TABLE tablename AUTO_INCREMENT = 1

    If your auto increment field is a primary key relating other tables then I highly recommend not doing that. The reason it does that is to prevent a relational database from becoming corrupted when used as a key to relate tables together.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    WA
    WA is offline
    Administrator
    Join Date
    Mar 2002
    Posts
    2,596
    Thanks
    2
    Thanked 19 Times in 18 Posts
    Thanks for the reply. I've tried your suggestion, and while the command ran ok, it didn't seem to alter the behavior of the table. Deleting a record in the middle of the table (ie: ID=2) and then inserting a new record still creates one with an ID of the old table's max(id)+1, with ID=2 being skipped:

    ID field: 1, 3, 4, 5, 6

    for example.

    I'll play around a little more. After all, that's just what I'm doing now anyway. It just seems that if an auto incrementing primary key cannot collapse after a record has been deleted, after a long time, you could end up with max(id)= millions, with gaps all over the place in between. That just doesn't sound right.
    - George
    - JavaScript Kit- JavaScript tutorials and 400+ scripts!
    - JavaScript Reference- JavaScript reference you can relate to.

  • #4
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Hmmm. Should have worked.

    What I was mentioning before was that if you change the value of a primary key field and that field is used to relate other tables then you would lose references to records in other related tables. Like for instance if your pk value was 3 and that 3 was stored in another table relating a record and you suddenly reset the pk and that 3 gets changed and no longer exists or get assigned to a different row then now either that related record no longer has a reference or it is now related to the wrong record.

    It does seem strange to have gaps but that is how it was designed; to help prevent corrupting the database. If you are planning on having millions of records in the database you are probably better off using a real RDBMS like Oracle. But you can also set that auto increment field as an index field as well to speed up finding records.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #5
    WA
    WA is offline
    Administrator
    Join Date
    Mar 2002
    Posts
    2,596
    Thanks
    2
    Thanked 19 Times in 18 Posts
    Originally posted by Spookster
    If you are planning on having millions of records in the database you are probably better off using a real RDBMS like Oracle.
    Not necessarily. What I mean is, lets say I write a guestbook script that gets 100 entries a day, or 3,000 a month. I may prune the guestbook every week for 30% of the oldest posts, but since the ID is auto incrementing, this field will keep growing regardless. That means in a few years, it will be millions, even though the guestbook remains at around few hundred or a few thousand records. The ID field will simply be 1 million 1 hundred to 1 million 6 hundred, with gaps all over the place.

    I understand the reason mysql doesn't auto fill in the blanks is to perserve the integrity of the primary key, though what if ID isn't the primary key, or even if it is, the coder wishes to write custom code that adjusts to this (kind of like a transaction).

    I'll definitely play around a little more with your earlier solution (alter table...). Thanks!
    Last edited by WA; 11-28-2002 at 10:37 PM.
    - George
    - JavaScript Kit- JavaScript tutorials and 400+ scripts!
    - JavaScript Reference- JavaScript reference you can relate to.

  • #6
    New to the CF scene
    Join Date
    Sep 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you are really concerned about that, then you could simply run these commands to "reset" that column. Simply put:

    ALTER TABLE table_name DROP COLUMN column_name;

    ALTER TABLE table_name ADD COLUMN column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

  • #7
    Senior Coder nikos101's Avatar
    Join Date
    Dec 2006
    Location
    London
    Posts
    1,005
    Thanks
    58
    Thanked 10 Times in 10 Posts

    Thumbs up

    Quote Originally Posted by mbenson View Post
    If you are really concerned about that, then you could simply run these commands to "reset" that column. Simply put:

    ALTER TABLE table_name DROP COLUMN column_name;

    ALTER TABLE table_name ADD COLUMN column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
    That worked great, but what would be need would be if any dependant tables could be mapped to the new values



  •  

    Posting Permissions

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