Auto Increment after delete in MySQL

Question

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.

Solution 1

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?

Solution 2

ALTER TABLE foo AUTO_INCREMENT=1

If you've deleted the most recent entries, that should set it to use the next lowest available one. As in, as long as there's no 19 already, deleting 16-18 will reset the autoincrement to use 16.

---

EDIT: I missed the bit about phpmyadmin. You can set it there, too. Go to the table screen, and click the operations tab. There's an AUTOINCREMENT field there that you can set to whatever you need manually.

Solution 3

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as is and add another column called for example courseOrder. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the courseOrder column of all rows that have courseOrder greater than the one you are currently deleting.

As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints.

Solution 4

Try :

SET @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

That'll reset the autoincremented value, and then count every row while a new value is created for it.

example : before

• 1 : first value here
• 2 : second value here
• X : deleted value
• 4 : The rest of the table
• 5 : The rest of the rest..

so the table will display the array : 1,2,4,5

Example : AFTER (if you use this command you will obtain)

• 1 : first value here
• 2 : second value here
• 3 : The rest of the table
• 4 : the rest of the rest

No trace of the deleted value, and the rest of the incremented continues with this new count.

BUT

1. If somewhere on your code something use the autoincremented value... maybe this attribution will cause problem.
2. If you don't use this value in your code everything should be ok.

Solution 5

You shouldn't be relying on the AUTO_INCREMENT id to tell you how many records you have in the table. You should be using SELECT COUNT(*) FROM course. ID's are there to uniquely identifiy the course and can be used as references in other tables, so you shouldn't repeat ids and shouldn't be seeking to reset the auto increment field.

View more solutions

Comments

[gabac]

what would you expect to happen if you deleted only record 16 ?

[OmidTahouri]

Good point! I should've mentioned. Ideally, shift everything up one?

[Web User]

I am running into an issue that is, in a way, the converse of your issue. In other words, I have an ENTITY table and an ENTITY_LOG table. Every time I insert, update or delete something from the ENTITY table, I log that activity into the ENTITY_LOG table, along with the entity's Id. Recently, a user deleted a bunch of entries in the ENTITY table; and corresponding log entries were made in the log table. Today, when another user tried to add a new entry into the ENTITY table, the generated Id happened to be the same as a previously deleted entity! I use JdbcTemplate and InnoDB.

[OmidTahouri]

@WebUser I don't see why that would happen. I'm pretty sure (and would expect) MySQL takes care of all that. Not sure of the JdbcTemplate side of things - maybe something in there is doing it(?). Good luck!

[Web User]

@OmidTahouri, I totally agree with that. It is certainly a weird issue. So I am going to try to replicate the problem and step through the code. Thanks for confirming!

[ninjalj]

@WebUser: Apparently, InnoDB doesn't persist the next ID number. So, the next time InnoDB starts up, it just looks up the max ID used.

[Charles Robertson]

Never ever recycle key values. You will run into big problems. See below.

[halfer]

@Kev: on this old question, you deleted the OP's answer, and appended it to the question. That is IMO going the wrong way: we try to encourage OPs to add their answer, and not to squash answers into a question edit (for correctness, in particular with the API). Would you be OK with my reversing it here?

[Mike Sherov]

The OP CAN do this, but he shouldn't. You should really reconsider this advice given what the OP is trying to do.

[Mike Sherov]

he's looking to maintain a count, not an ordering. The UPDATE seems unnecessary.

[Darin Dimitrov]

Well if he is looking to maintain a count then there's no need to add additional columns. The simple count aggregate function will do the job.

[OmidTahouri]

Okay, thanks. A lot of answers/comments in such short time! :O I'm trying to take them all in. I will look into the count function :)

[OmidTahouri]

You're correct. It's best to ignore it. Considering this is only for an assignment, I won't be going into high numbers and the lifetime is very short.

[monksp]

It's not my place to tell him how to lay out his database, or how to do his business logic. He also mentioned in his post that he's read other posts/pages stating that it's a bad idea, so he knows that it's not a recommended practice, but is going ahead with it anyway.

[PurplePilot]

agree highly dangerous for a number of reasons. One, in a multi user system you may have many, hundreds, thousands of updates per second and trying to rewrite the auto inc could either slow the system down or compromise it. Two another developer would not know you were =doing this perhaps and link records through the id therefor corrupting the system. etc.

[Air]

This is the most direct answer currently provided to the asker's only explicit question. There's no "advice" involved.

[ToBe]

Allways a good "practice" though to also advice not to use your solution if you have reasons against it the OP might have missed.

[Claod]

If you want to preserv old values and set the auto_increment to the max value then you need find the max value: 1. By ordering them, and take the value of the last row/ 2. and then set auto increment to this max value +1 in your code.

[Charles Robertson]

I think it is our place to advise against bad practice. And believe me, recycling key values is very bad practice. DO NOT DO THIS UNDER ANY CIRCUMSTANCES.

[Charles Robertson]

I think he thinks it is some kind of bug. MySQL is 20 years old. This is definitely not an oversight. There is a very good reason why auto increment does not recycle keys. You are totally correct Mike.

[Jojodmo]

Although this is a bad practice, it is the best answer because it actually answers what the OP asked

[Nils Sens]

Good points. The answer made me think twice. I for one was looking for no gaps in the auto_incr. because I was intending to somehow loop through the ids. But I guess it's better to just loop through the rows :)

[liamvictor]

MySQL is a relational database. When there are several tables there needs to be a relationship defined between the tables. And that is done using ID columns. The problem with this approach is that if you start renumbering the indices of a table there will be knock on effects on all the tables it has a relationship with.

[Aloui Khalil]

link

[James McCormac]

I agree, so long as you understand the consequences, this is the right answer.

[Michael]

So it is simply not possible? There are valid reasons for wanting to do this in a test environment.

[Ariful Islam]

Really appreciating. Its help me a lot to solve my friend's problem. I found this answer after a lot of search on the internet.

[Pablo Alexis Domínguez Grau]

It's also useful when you're just adding test values to a database and the fact that, after you deleted some test rows, it bothers you that the IDs won't match up anymore. Easy fix by doing this command.

[Stuart Harland]

I get a liitle miffed when people criticize people's reasonable attempts to answer the question just because it's not good practice. Just because in this use case altering the auto-increment counter is a bad idea, doesn't mean that there are no circumstances where this is desirable. Use cases differ. What if OP had been trying to do an administrative reset without having the ability to run drop table (as an example)? Not sure Why OP would care about the keyspace, but that's not your call. By all means, explain that it's not a good idea, but refusing to answer devalues StackOverflow.

[Stuart Harland]

Of course it is possible. MySQL has a schema, but it doesn't have a rich understanding of the schema. The semantics are derived within the application. You have integers. Not class room identifiers. It has no concept as to what the semantics are, what you do with that, how it may join up with other entities, foreign keys can't apply outside of the database to other data-structures. Ergo the idea of "rationalising the keyset" just isn't a concept within MySQL: that would fall in the domain of the application, but it's almost always a bad idea for data integrity to attempt this.