Antipattern: Filling in the Corners
Understand the risks and inefficiencies of filling gaps in primary key sequences, including concurrency issues and data integrity problems. Learn why reusing or renumbering primary keys can cause errors and conflicts, and discover best practices to maintain reliable, consistent database records without recycling key values.
We'll cover the following...
We'll cover the following...
There are two ways we might fill the perceived gap.
Assigning numbers out of sequence
Instead of allocating a new primary key value using the automatic pseudo key mechanism, we might want to make any new row use the first unused primary key value. This way, as we insert data, we naturally make gaps fill in.
| bug_id | status | product_name |
|---|---|---|
| 1 | OPEN | Open RoundFile |
| 2 | FIXED | ReConsider |
| 4 | OPEN | ReConsider |
| 3 | NEW | Visual TurboBuilder |
However, we have to run an unnecessary ...