Prevent gaps/holes in a MySQL table AUTO_INCREMENT Primary Key field.

Generally during the MySQL database development you create tables with a Primary Key, a very simple way to create a primary key is make it Auto-increment and unsigned integer with Not Null property right?

I think most of you generally follow the above process. Suppose you are playing a very large database and have a very large number of Ids, I know this is rarely happens but you should aware this one, you might face an issue where the new row insertion will through error.

Have a look on MySQL data type and their range

INT – A normal-sized integer that can be signed or unsigned.
– If signed, the allowable range is from -2147483648 to 2147483647.
– If unsigned, the allowable range is from 0 to 4294967295.
– Max allowed length is 11 digits.

TINYINT – A very small integer that can be signed or unsigned.
– If signed, the allowable range is from -128 to 127.
– If unsigned, the allowable range is from 0 to 255.
– Max allowed length is 4 digits.

SMALLINT – A small integer that can be signed or unsigned.
– If signed, the allowable range is from -32768 to 32767.
– If unsigned, the allowable range is from 0 to 65535.
– Max allowed length is 5 digits.

MEDIUMINT – A medium-sized integer that can be signed or unsigned.
– If signed, the allowable range is from -8388608 to 8388607.
– If unsigned, the allowable range is from 0 to 16777215.
– Max allowed length is 9 digits.

BIGINT – A large integer that can be signed or unsigned.
– If signed, the allowable range is from -9223372036854775808 to 9223372036854775807.

– If unsigned, the allowable range is from 0 to 18446744073709551615.
– Max allowed length is 20 digits.

Let a suppose you have specified your primary key as INT so the max number you can enter with in it is 4294967295 (if unsigned),
Yes it is not a small number and will not reach generally but if the system allow deletion of records/data on daily basis then what will happen, one day a new row will not insert and it will reach a max limit as you know once a row deleted that id will not use any more and you will have a lot of holes/gaps in the id that was not used.

I think you have got what I want to say. You have a table named as student with simply 2 columns
id and name and your table data are

1 student1
2 student2
3 student3
.. ..
20 student 20

Now you have deleted 2 and 3 id’s records then these id will never user by auto-increment id (primary key), you can use these id but you should know which id is not used and how to fetch this automatically, it will very hard to find if there are lot of rows.

How to fill or use this id or holes or gaps automatically.
Use the below query to insert the id instead of use this automatically. It will return NULL if no gap/hole is there in the primary key.

SELECT MIN(st1.id+1)
FROM student st1
LEFT JOIN student st2 ON st2.id = st1.id + 1
WHERE st2.id IS NULL

The above query returns you an id that can be used to insert the new row.

There are some most important note you should take care before using these.

You should not have values (id) on other tables associated (as a foreign key) with the deleted primary key. If this happen your rest of life will not fine. You should take care that when you insert a new entry with primary key that was taken from the gap, the gaped value should not be there in the associated tables.

You should clean ALL of your tables when you delete a primary key.