Sample Header Ad - 728x90

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