Menu
I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.
I have:
table: course fields: courseID, courseName Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.
My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?
This table will relate to others in a database.
Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) business meaning.
For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.
765
Reset the auto increment field ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design. Use DELETE FROM table instead.
In MySQL, the TRUNCATE TABLE command will reset AUTO_INCREMENT values, as shown in the following example. If this is a problem, you can reset the AUTO_INCREMENT value using the ALTER TABLE command.
ALTER TABLE suppliers AUTO_INCREMENT = 1; This example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 1 for the supplier_id column in the suppliers table.
What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.
If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.
Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?
Are you really going to have more than 18,446,744,073,709,551,615 unique records over the course of your application's lifetime?
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
