CODEWITHSUNDEEP
mysql
user2605793
user2605793

Reputation: 471

Re-use of deleted MySql keys

Everyone says don't re-use deleted MySql keys. eg. Stack Overflow question: I want to reuse the gaps of the deleted rows

I have read all of the "expert" opinions but have not found a single answer that gives a valid reason why not. Everyone simply asks "why do you want to"?

Well here is a very good reason. If my users have a choice of entering URL mysite.com/person.php?id=123 or a URL mysite.com/person.php?id=123456789123, which one would they most likely prefer?

So can anyone give me a reason why re-using 123 would be a bad idea? I am actually not talking about one record. My records get added and deleted in blocks of several thousand. Updates are very rare and I am the only person who does updates.

There are also no dependencies. Nothing points to those records so there are no integrity issues with other tables.

When I want to add another block of records I will have a simple search routine that searches for the first block of unused record keys large enough to accommodate all of the records being added. Much the same way that hard disk space usage works.

Upvotes: 2

Views: 90

Answers (1)

Noam Rathaus
Noam Rathaus

Reputation: 5608

Keys are usually used as unique identifiers, if they are used again, they stop being unique, and become shared. This is the logic behind the idea of not to reuse keys.

So I would suggest, split the key and the id of the user, to two fields, key the key as unique, and the id make it "choose-able" via a gap-finding function.

Before you split, create this new column called user-id, and copy to it the id (which is currently your key) of the users.

Then make this column unique, so that you prevent accidental cases of id reuse.

And you are "home" free.

Upvotes: 1

Related Questions