CODEWITHSUNDEEP
mysqlauto-increment
SnarkyDTheman
SnarkyDTheman

Reputation: 265

MySQL: Auto increment minus one?

I have some code that will delete a post from the database:

mysql_query("DELETE FROM `posts` WHERE ID = '$id'");

Now I want to set the auto increment minus one to keep up with the deleted post. Is this possible? If so, how can I do it? Thanks!

Upvotes: 1

Views: 1978

Answers (3)

corsiKa
corsiKa

Reputation: 82559

You really don't want to do this.

Imagine the following happens:

A new post is made with id 100 A new post is made with id 101 A new post is made with id 102 Post 100 is deleted... your post counter is now at 101 A new post is made with id 101

So now you have two ids with 101? If your constraints let you, that's bad. If they don't let you, it's impossible.

Either way, it's best to just let the 'id 101' die forever. It won't be missed, there's a few billion more numbers to pick from...

Upvotes: 2

Marius Solbakken Mellum
Marius Solbakken Mellum

Reputation: 736

alter table yourtable auto_increment=5

Perhaps a subquery can be used to get the count:

alter table yourtable auto_increment=(select count(*) from yourtable) + 1

Are you really sure this is what you want to do? Remember that this will break if you delete a row "In the middle" and not the last one.

Upvotes: 1

kitti
kitti

Reputation: 14794

You can manually set the AUTO_INCREMENT value (http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html), but that's a bad idea.

First, say there are 5 posts. If the post with ID 3 is deleted, you can't really set the auto-increment to 3 because IDs 4 and 5 are still in use.

Second, if there are any references to that ID in another table that didn't get deleted then those references will be bad when that ID is reused.

In general, auto-increment values are meant to be used once only. You can get around this if you want, but there's really no reason to.

Upvotes: 5

Related Questions