AUTO_INCREMENT help
Posted: Tue Jan 10, 2006 1:21 pm
I am having an issue and I have a way of getting around it. However, I don't like my way and it seems like there should be an easier "built-in" way of doing it. Have a look and see if you can think of a better way...
I have some product tables. The tables have an "id" column, which is used in iterating through the table in later loops. Thus, it is very important that the id column always starts at 1 and goes *without skipping any numbers* up to the max row number.
I am building an administrative page where I can add and delete products from these tables. Whenever I add or delete a product, I need the auto_increment "id" column to either fill in a hole from the newly deleted product, or add on to the end for an added product. However, as it stands this does not happen.
When I delete, say, row 45 from a table, the next product I add will not fill in row 45, but will instead tack onto the end and auto_increment from the largest value in id. Furthermore, if I delete all but one of 450 values, my id column may later end up going from 1 straight to 451 when I add another product.
My solution is this process:
1. I go in and add or delete a row
2. mySQL pulls all values from product table, and puts them in a temporary table
3. mySQL deletes all entries in the old product table
4. mySQL pulls all product info from the temp table and rewrites it to the product table (resetting auto_inc)
5. mySQL deletes the temporary product file
This will effectively restart the auto_increment count every time a product is added, but it risks losing all product data if somehow an error occurs while the data is being moved around. It's unlikely, but could result in the end of the world. Is there a better way?
I have some product tables. The tables have an "id" column, which is used in iterating through the table in later loops. Thus, it is very important that the id column always starts at 1 and goes *without skipping any numbers* up to the max row number.
I am building an administrative page where I can add and delete products from these tables. Whenever I add or delete a product, I need the auto_increment "id" column to either fill in a hole from the newly deleted product, or add on to the end for an added product. However, as it stands this does not happen.
When I delete, say, row 45 from a table, the next product I add will not fill in row 45, but will instead tack onto the end and auto_increment from the largest value in id. Furthermore, if I delete all but one of 450 values, my id column may later end up going from 1 straight to 451 when I add another product.
My solution is this process:
1. I go in and add or delete a row
2. mySQL pulls all values from product table, and puts them in a temporary table
3. mySQL deletes all entries in the old product table
4. mySQL pulls all product info from the temp table and rewrites it to the product table (resetting auto_inc)
5. mySQL deletes the temporary product file
This will effectively restart the auto_increment count every time a product is added, but it risks losing all product data if somehow an error occurs while the data is being moved around. It's unlikely, but could result in the end of the world. Is there a better way?