When should a primary key be meaningful?
8
votes
3
answers
5776
views
Excuse the looseness of any definitions here, but I am trying to explore a simple concept.
A __Primary Key__ uniquely identifies a row. There are possibly other columns in a table with unique values, so they can also uniquely identify a row (Candidate Keys), but the Primary Key is the one designated for the task.
Properties which make a Primary Key more useful include:
- guaranteed to be unique; some other unique column values may be duplicated as the table grows
- unlikely to change; although Foreign Key constraints can include cascading, it is better not to require this
- will not be recycled; a row which is deleted for some reason should not free the old PK value to be reused
For these reasons, I generally recommend that the Primary Key have no intrinsic value, so there would never be a reason to change or recycle a value. That is, it should be otherwise __meaningless__.
I have seen Primary Keys which include some sort of code, such as a client code based on the name. The obvious problems are (a) if the client name changes then the PK should change too, and (b) there is too much of a risk of conflict with clients with similar names.
A half exception is the use of an auto-incremented number, which has a minor meaning of a sequence number. However, it is still stable.
The question is, under what circumstances, if ever, is preferable to use a primary key with some other real meaning? That is, is there anything wrong with the advice that the PK should be arbitrary, and you can generally get by with a sequence number?
Asked by Manngo
(3145 rep)
Mar 18, 2018, 07:08 AM
Last activity: Mar 28, 2018, 10:20 PM
Last activity: Mar 28, 2018, 10:20 PM