0

I have a blog with different posts. Let's say I have 10 posts. When I choose to delete the latest post with DELETE FROM table WHERE id = 10, it deletes it.

But then, the next post I make, gets the ID 11.

So my database looks like:

ID | post
11 | foo
9  | bar

But the number 10 I delete no-longer exists. How can I make it so that the next post I'd make gets ID 10?

The ID column is auto-increment.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
TyhaiMahy
  • 85
  • 1
  • 2
  • 8
  • not bugged at all, completely working as expected. –  Jul 09 '15 at 22:05
  • you can shoot yourself in the foot and do an explicit insert with id=10 and get away with it mostly. or you can shake that OCD fixation and not do that. the mentality of those worrying about gaps are probably owners of db's without FK RI and just hung a new parent over the orphans – Drew Jul 09 '15 at 22:07

1 Answers1

3

I think the best answer is you don't.

This is the expected (and correct) behavior for a relational database.

Say you have comments that are related to your blog post by the post's ID. If you delete the post and keep the comments, changing another post's ID to the old ID would result in the comments getting related to the wrong post.

This is only a trivial example. This type of data relation error has the potential to cause havoc in a database. The IDs auto increment that way so as to guarantee uniqueness.

mjohns
  • 369
  • 2
  • 11
  • In my case, everything related to the post gets deleted. – TyhaiMahy Jul 09 '15 at 22:00
  • 2
    What are you trying to achieve by forcing the ID values to the next available? What if you have 12 posts and delete number 4? – mjohns Jul 09 '15 at 22:03
  • In your current example maybe @TyhaiMahy. What if another developer comes in and creates a task that deletes only this table's record and not the other related records? You shouldn't recycle IDs. An alternative approach could be making a temporary table where you store data until you are certain it should be assigned an ID then transfer it to permanent table. – chris85 Jul 09 '15 at 22:03