Forum Moderators: open
Ok.. f'rinstance:
no. name
1 Steve Jobs
2 Bill gates
3 Chuck Norris
4 Jessica Alba
Ok, so i delete Bill gates, so now my database is without a person no.2
how can i put a new person in there?
at the moment, when i add a person, it just auto increments it, but i want to check if theres any numbers free 1st.
Heres my code so far:
?>
<form action="<?php echo($self);?>" method="post">
Forename:<input type="text" name="fname" size="8">
Surname:<input type="text" name="sname" size="8">
<input type="submit" value="Add to database"> </form>
<?php
if($fname and $sname)
{//creating a query
$sql="insert into guest_data (surname, name) values (\"$sname\", \"$fname\")";
//run the query
$rs = mysql_query($sql, $conn);
//confirm its worked
if($rs){ echo("Record added!");}
}
?>
Im using mySQL administrator GUI to add schemas and suchlike
(if this'd be better in the PHP forum, feel free to move it)
The purpose of autoincrement numbers typically is to simply guarantee you have a unique single field key that you and the DBMS can use to quickly and uniquely identify a row in a table. The value itself is not important. It's perfectly ok to have holes in the numbering of your rows.
You may only have a transactional system today. But if later you want to maintain an additional database for some type of data warehouse with historical data in it, you may have a need to delete a key from your transactional system but that same key may still exist and have meaning in your data warehouse (maybe you only keep 30 days worth of data in your transactional system so your site is fast, but you keep 3 years worth of historical data in your data warehouse). In this case, reusing an old key value in the transaction will ruin or invalidate your historical data for that key.
Just use the next autoincremented value for new records as it is meant to be used...
That being said... It can be done but I DEFINITELY wouldn't suggest it. It will GREATLY slow down inserts as you'll have to manually loop through the table starting at 1 up to the last key value handed out by the DBMS to find out if any holes exist. If you do find a value that is not used you can usually turn off the AUTOINCREMENT on that column temporarily via an ALTER TABLE statement, insert the row explicitly stating the keyvalue you want to use, and then turn AUTOINCREMENT back on via ALTER TABLE for the column reseeding it to MAX(keyvalue)+1. I'm not sure about the specific syntax for MySQL. I'm more Oracle/SQL*Server experience.
But imagine what would happen if you had to do this every time you wanted to insert a row into that table and the table had 10s of thousands or even millions of rows...
I really don't think you want to go there.
Thanks for that. Very good points.
I guess i can can just hide the auto increment number from the user, and have it only avail for admins.
On a similar note, how big can a number in an SQL database be?
I mean i wouldnt want to risk and buffer overflow errors in, say, 5 years time?
Although i could develop a backup system... per year f'rinstance, which archives the lot, then restarts the incrementation...
Any thoughts?
As you approach 2,147,483,647 (assuming the earlier numbers 1-? have already been deleted) you can reseed the auto-increment column and tell it to begin handing out numbers at 1 again. But my guess is you'll not run out of values any time soon.
Even at 100,000,000 (100 million!) rows per year, you have about 21.5 years before you'll need to reseed it.
