Use of a running sequence number as the
primary key instead of a logical key gives rise to the following issues:
·
For new creations the access is always
towards the end of the table
·
Contention in an multi-user environment
for accessing the last sequence number
Best Practice:
1. One
solution is to a common table, which contains only two attributes: table name
and last sequence number. Being a small table, this should resolve the
contention problem to a great extent.
2. Instead
of creating the primary key ASIS, the key is inverted and created. This will
resolve the creation of new records towards the end of the table.
For eg:
If the primary key is a 6-digit number,
the new records are created as follows:
·
100000
·
200000
·
...
·
900000
·
010000
·
110000
·
210000
·
...
Note: One could also use a random number
generator to create unique primary key. See a post to create a random number using program..
No comments:
Post a Comment