Certain database operations such as the creation of new records, often result in skipped primary key numbers. This often happens in some database engine types and result in tables with non-consecutive record ID numbers or record ID gaps. I will be discussing manual ways of plugging up or preventing these gaps in your relational database system.
This technique involves locating the last record ID number in the table, manually incrementing it by 1 before creating a record with the new number. I wrote a simple procedure that does just this. The advantage of this is that the function ensures that a valid unused record ID consecutive to the last in the table is always used. However, this method does not solve already existing gaps in the database.
This technique will involves using a CRON script to scan a table or group of tables, locating gaps (non-consecutive record IDs). These gaps are logged to a file or table. These could be in the form of number pairs, defining where the hole was found, e.g. 3-5, 49-52, 454-460 etc. Or they could stored as number ranges in CSV format such as (4), (50,51), (456,457,458,459) etc. The log is then checked every time a record is to be created to a table, for a list of unused record IDs. Once a record ID is picked and used for record creation, the number is removed from the list. The script should be able to revise its record IDs for every scan.
The advantage here s that the previous gaps in a system can be efficiently plugged. When combines with 1. above, gaps will be completely eliminated in new records. This technique involves a lot of moving parts, something could go wrong. However I like this because it presents a learning challenge.
This technique will involve move around records to close gaps in its record ID numbers. The script here will scan and locate gaps and patch up these holes with already existing records, assigning them new numbers.
In a good design, this script should be able to run once correcting all holes in a table instantaneously as against the other methods which only work when a record is about to be created. The downside to this method is that it could get a whole lot confusing to implement for normalised tables or tables whose primary IDs refer to secondary keys in other tables. Hence, this method should be used in small, non-volatile databases where all tables and relationships have been properly mapped and does not change often.