How to get deleted ID (primary key) and regenerate for new user?

Question

I'm using Laravel 7.

I want to regenerate the deleted primary key ID. For example, my database ids are:

================
id   | username|
================
1    |  john   |
2    |  abc    |
3    |  xyz    |

If any user deletes account like ID 2, my database look like follows:

================
id   | username|
================
1    |  john   |
3    |  xyz    |

so, I want to get the gap ID, and create new user like with that ID.

================
id   | username|
================
1    |  john   |
2    |  mno    |
3    |  xyz    |

put the type of database you are using. ex: MYSQL, PostgreSQL, MS SQL,... — Ibrahim W., Jun 18 '21 at 05:26
Just dont. Stick to autoincremented ids. Reusing deleted ids causes more harm than good — Arun A S, Jun 18 '21 at 05:35

score 0 · Answer 1 · answered Jun 18 '21 at 05:37

0

I don't know why you'd want to change the user an ID is pointing to. The point of an ID is that it is unique. I would not recommend changing the user of an ID.

I recommend that you refer to this question: How to update primary key

answered Jun 18 '21 at 05:37

I don't want to change the ID. I want regenerate non-existence IDs or deleted ids. suppose my primary key have 2 digit unique id... So I can insert records or users(for example) to 01 to 99.. if few ids or records are deleted from middle... (like 33,54,61,....), it will be able to used for user id of new records or users...its save my ids... either I have to create new ids with three digits values... is it possible to do something like that ? Thanks... – ANUPAM DHAR Jun 18 '21 at 06:08

Ibrahim W. · Accepted Answer · 2021-06-18T16:21:47.130

Manually using a unique id

you can ALTER the table you have so that: id column is a VARCHAR(13) and removing AUTO_INCREMENT, manually adding unique ids ex: <?php $id = uniqid(); ?> which generates a unique id using the timestamp, ex: output: (string) 60cc324a512d6

Manually using a unique id which is hashed with md5 hasing algorithm

if you want more uniqueness use <?php $uniqueId= md5(uniqid()); ?> which generates a unique id using the timestamp and then hasing it using md5() hashing function ex: output: (string) f71d3e810cdcc8008429d31d3a6459d4 , and then alter the table to be id -> VARCHAR(32) and removing AUTO_INCREMENT

Ok what to use?

Using the normal AUTO_INCREMENT is best even if you have ocd like me and want to fill those deleted ids, but it is up to you to use any.
md5 hasing algorithm will generate a harder to guess id, and is more safe, since uniqid() function uses timestamp to generate that id it can be guessed via hackers by generating ids using timestamps, even though slower but safer to use a string of 32 character as an id, hope this helps

How to get deleted ID (primary key) and regenerate for new user?

2 Answers2

Manually using a unique id

Manually using a unique id which is hashed with md5 hasing algorithm

Ok what to use?