Table's

[tappiocca]

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.

[Cute Tink]

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.

[Jake Arkinstall]

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:

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.