SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Table's

  1. #1
    SitePoint Zealot
    Join Date
    Apr 2011
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table's

    Dont know why I named the question Table's.. o well
    So I am creating a database for my class, it is rather basic. It includes a INT, a TEXT, and a DATE ie.
    1 xxxxxx 2011-4-4
    2 xxxxxx 2011-4-4

    My question is, when I delete both lines in the DB, why does the DB not backtrack to a empty INT (which should be 1), yet it moves onto 3. Is it b/c of the auto_increment that I added? Or do I have tell it to look for the next vacant INT and then go from there.. Note: I am a beginner, so I am just curious.

  2. #2
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you set it to auto-increment, then it will remember the ID of the last insert for the table.

    If you want it to reset, you can use this query: truncate table myTable

    where myTable is the name of the table you want to empty. That will reset the auto increment.

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The reason it does this is to maintain the integrity of your data.

    For example, imagine you have a table called Horses, another called Races and another called Race_Horses. Horses contains information on horses, Races contains race information and Race_Horses lists each horse in each race, e.g:
    Code:
    Horses:
    ID   |       Name       |  Jockey
    1    |    Crosseye      |   Mickey Jones
    2    |    Lucky Saddle |   Philip Roberts
    
    Races:
    ID   |     Date       |     PrizeTotal
    1    |   01/04/2011 |     10000
    2    |   02/04/2011 |     25000
    
    Race_Horses:
    Race     |    Horse    |   Position
       1       |      1        |      2
       1       |      2        |      5
       2       |      1        |      8
       2       |      2        |      1
    If you delete the entry #2 from horses without deleting the entries in Race_Horses, and the next ID did reset to 2, then the next horse added would automatically have data linked to it from Race_Horses, and according to its information page would have won a race on 2nd April, which it wasn't really a part of.

    The whole point of an ID is to identify a record. If an ID in a table belongs to a record, that ID should ALWAYS be reserved for that record even if it isn't there any more.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


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
  •