I have a user table that has 4 rows with id = 1, 2, 3, 4. I delete the row with id = 2 and insert another user and the user will get id = 5. What will happen to the id = 2? Will it be used again or do I rearrange the ids? Rearranging ids doesn't make sense if I have a large amount for every delete operation.

  • 2
    Is this specific to a particular RDMS? ID fields are usually auto incrementing, but depending on your engine, the specific functionality can vary a little. – e_i_pi Jan 14 at 23:43
  • What do you mean by rearranging? Surely not some kind of renumbering?? – Erik Eidt Jan 14 at 23:51
  • I have the table in Postgresql, I guess it is a relational database. Sorry, I am not used to the DB terms. Yes, I understand that it will increase the number by itself. Rearranging here means like I removed the user with id = 2, so the next thing I do is to change id of user with id = 3 to id = 2, user with id = 4 to id = 3 – calvert Jan 15 at 0:10
  • 2
    Generally speaking it is a very bad idea to reassign ids, because they are a kind of name. It would be very confusing indeed if today cat stood for a four legged creature with fur, and tomorrow it only represented a star prior to supernova. Similarly the 2 in this table was associated with a specific definition. RDBMS systems are aware of this, and one approach (but no the only approach) is to assign the next highest number. Eventually you may have identified enough rows that there are no larger integers in the id. In this instance the RDBMS will either complain, or reuse deleted ids. – Kain0_0 Jan 15 at 0:50
  • Thank you for the explanation. I had the same thought, assign enough rows for the ID. This clarified everything I need to know for my first app. – calvert Jan 15 at 3:07

You don't do anything. The deleted IDs are gone forever.

No, that isn't a problem.

The primary purpose of the ID is simply to be unique. Deleting some IDs never to be seen again doesn't affect the uniqueness of others. Most DB engines provide an automatic ID assignment capability, you should use it.

Just make sure you allocate enough bits in your ID to provide enough room during your application's life, that's all you have to do. Do not try to roll an id recycling system.

  • Thank you for the explanation. Knowing this ease my mind a lot. – calvert Jan 15 at 3:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.