I just need a logic on how to Reset Autoincremented ID to the previous ID which I deleted recently from database for example I just delete ID=3 from database next time I add data into database ID will be incremented to 4 and it will be showed as 1 2 4 but I dont want 4 I want it to be 3 Im building a small inventory management system using php and sql.
Asked
Active
Viewed 167 times
0
-
If you use PHPMyAdmin you can set the AUTO_INCREMENT under the Operations tab of your table. If you need to do it with a query it would be something like `ALTER TABLE foobar AUTO_INCREMENT = 123` – ArendE Feb 07 '21 at 00:31
-
1What is the purpose of such an requirement? – Tpojka Feb 07 '21 at 00:57
-
This is depending on your database. What kind of database do you use? Please provide us with more details. – not_a_bot_no_really_82353 Feb 07 '21 at 01:45
1 Answers
0
The following query will set your AUTO_INCREMENT value to 1 less than what it currently is, however I would recommend you don't bother doing so - recycling key values is not recommended practice and can be risky as it's not the way AUTO_INCREMENT is intended to be used (see answers to this question).
ALTER TABLE TableName AUTO_INCREMENT =
(SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName') - 1;
Oliver Dalton
- 379
- 2
- 16