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.
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:
[U][B]Horses:[/B][/U]
ID | Name | Jockey
1 | Crosseye | Mickey Jones
2 | Lucky Saddle | Philip Roberts
[U][B]Races:[/B][/U]
ID | Date | PrizeTotal
1 | 01/04/2011 | 10000
2 | 02/04/2011 | 25000
[U][B]Race_Horses:[/B][/U]
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.
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.