How to Reuse Deleted Primary Keys in MySQL

how to reuse deleted primary keys in mysql?

Using:

ALTER TABLE [your table name here] AUTO_INCREMENT = 1

... will reset the auto_increment value to be the next based on the highest existing value existing in the table. That means it can't be used to correct gaps of more than one.

The only reason to do this would be for cosmetic ones - the database doesn't care if records are sequential, only that they relate to one another consistently. There's no need to "correct" the values for the database's sake.

If you are displaying the id values to the user, which is why you'd like them to always be sequential, then I'd recommend adding a surrogate key. Use the surrogate key for displaying to the user, so the values can be re-sequenced as needed but referencial integrity is otherwise unaffected. The surrogate key in this case would be an integer column.

How to Prevent Reuse of Deleted Primary Key Values in Table?

The only foolproof solution is to not delete the rows. Or at least don't delete the row with the highest auto-increment value.

How can I delete a database record and reuse the deleted primary key?

I presume you are using auto-increment to assign a new key value to your Id column. You are assuming that since there are no records then the next free value would be 1, but it just keeps incrementing from the previous highest value.

Its a bad idea, but if you really wished to recycle your key values then you could switch off auto-increment and manually manage your key values yourself, but this is error prone and difficult.

Do you really need to do this? Int and BigInt can hold very large numbers, are you likely to ever run out of key values so that recycling might be required?

If you just want to reset your auto-increment back to 1 I suggest you look at this post

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

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

Will MySQL reuse deleted ID's when Auto Increment is applied

InnoDB resets the auto_increment field when you restart the database.

When InnoDB restarts, it finds the highest value in the column and then starts from there.

This won't happen in MyISAM because it caches the last incremented id.

Update

This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,

1. Table t1 has an auto-inc primary key.

2. Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.

3. As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.

This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB
persistent max value for autoinc columns.

InnoDB will keep track of the maximum value and on restart preserve
that max value and start from there.

Do numerical primary keys of deleted records in a database get reused for future new records?

NO. numerical primary keys will not reused, except you specify them manually(you should really avoid this!)

will ms sql server reuse deleted primary keys in auto increment mode?

MSSQL will not re-use the primary keys that have been deleted using DELETE (I'm assuming you are talking about the identity incrementation.) If you TRUNCATE the table it will reset the seed and reuse them.

If you go over the max for INT it will indeed just fail to make the next row. You can convert your INT column to BIGINT to avoid that.

BIGINT has a max of: 9,223,372,036,854,775,807 and INT has a cap of 2,147,483,647 but note.. you can also use those negative values too!

You can read about those caps here: https://learn.microsoft.com/fr-fr/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

---

Related Topics