Choosing Primary Keys
Each item in the PERSONNEL file has a Person ID that is unique to that person. See Example of a PERSONNEL Item. This may be used within a company to uniquely identify personnel, or it may be assigned by a computer. Either way it uniquely identifies a person's record within the PERSONNEL file. The Person ID in this example is called a 'primary key'.
The Person ID in the QUALIFICATIONS table is called a 'foreign key' A foreign key is a column or combination of columns in one table, whose values are required to match those of the primary key in a second table. The primary key in the QUALIFICATIONS table is formed by a combination of the Person ID and the Qualification No. (multivalue number).
Note
Almost without exception, every table should have a primary key. A table that has no primary key cannot be updated.
Where an SQL table is based on a Reality file the column or columns that correspond to the Reality item-id are generally a good choice.
Where multi- or sub-valued data is being exploded into multiple rows another column should be added to the Primary Key to uniquely identify the multi- or subvalued set. If there is no suitable column in the data itself, an artificial column should be created such as the QUAL_NO column discussed earlier. See Example 3.