CODEWITHSUNDEEP
mysqlsql
nerd
nerd

Reputation: 78

mysql delete,autoincrement

I have a table in MySQL using InnoDB and a column is there with the name "id".

So my problem is that whenever I delete the last row from the table and then insert a new value, the new value gets inserted after the deleted id.

I mean suppose my id is 32, and I want to delete it and then if I insert a new row after delete, then the column id auto-increments to 33. So the serial format is broken ie,id =30,31,33 and no 32.

So please help me out to assign the id 32 instead of 33 when ever I insert after deleting the last column.

Upvotes: 4

Views: 4014

Answers (4)

mvp
mvp

Reputation: 116068

You should stop fighting this, even using SELECT max(id) will not fix this properly when using transactional database engine like Innodb.

Why you might ask? Imagine that you have 2 transactions, A and B, that started almost at the same time, both doing INSERT. First transaction A needs new row id, and it will use it from invisible sequence associated with this table (known as AUTOINCREMENT value), say 21. Another transaction B will use another successive value (say 22) - so far so good.

But, what if transaction A rolls back? Value 21 cannot be reused, and 22 is already committed. And what if there were 10 such transactions?

And max(id) can assign the same value to both A and B, so this is not valid as well.

Upvotes: 3

Pomp
Pomp

Reputation: 59

I suppose you mean "Whenever I delete the last row from the table", isn't it?

Anyway this is how autoincrement works. It's made to keep correct data relations. If in another table you use an id of a record that has been deleted it's more correct to get an error instead of get another record when querying that id.

Anyway here you can see how to get the first free id in a field.

Upvotes: 0

user330315
user330315

Reputation:

You have two major misunderstandings about how a relational database works:

  1. there is no such thing as the "last row" in a relational database.
  2. The ID (assuming that is your primary key) has no meaning whatsoever. It doesn't matter if the new row is assigned the 33, 35354 or 236532652632. It's just a value to uniquely identify that row.

Do not rely on consecutive values in your primary key column.

And do not try the max(id)+1 approach. It will simply not work in a system with more than one transaction.

Upvotes: 4

fancyPants
fancyPants

Reputation: 51868

Short answer: No.

Why?

  1. It's unnecessary work. It doesn't matter, if there are gaps in the serial number.
  2. If you don't want that, don't use auto_increment.
  3. Don't worry, you won't run out of numbers if your column is of type int or even bigint, I promise.
  4. There are reasons why MySQL doesn't automatically decrease the autoincrement value when you delete a row. Those reasons are
    • danger of broken data integrity (imagine multiple users perform deletes or inserts...doubled entries may occur or worse)
    • errors may occur when you use master slave replication or transactions
    • and so on ...

I highly recommend you don't waste time on this! It's really, really error prone.

Upvotes: 17

Related Questions