The unique key of a table is a column or an ordered collection of columns for which each value identifies (functionally determines) a unique row. For example, an employee number column can be defined as a unique key, because each value in the column identifies only one employee. No two employees can have the same employee number.
The primary key of a table is one of the unique keys defined on a table but is selected to be the key of first importance on the table. There can only be one primary key on a table.
A primary index is automatically created for the primary key. The primary index is used by the database manager for efficient access to table rows and allows the database manager to enforce the uniqueness of the primary key. At other times the database manager may use other columns with indexes defined, and not only the primary key and index, to access data when processing queries.
Several columns could qualify as a candidate to be the primary key for a table. Each of the candidate columns could be considered unique. You could have all of the columns as part of the primary key but this would create an overly complex primary key. You should consider having just one of the columns as the primary key and then creating unique constraints or unique indexes on one or more of the other columns.
In some cases, using a timestamp as part of the key can be helpful, for example when a table does not have a "natural" unique key or if arrival sequence is the method used to distinguish unique rows.
Primary keys for some of the sample tables are:
The following example shows part of the project table with the primary key
column indicated.
Table 4. A Primary Key on the PROJECT Table
| PROJNO (Primary Key) | PROJNAME | DEPTNO |
|---|---|---|
| MA2100 | Weld Line Automation | D01 |
| MA2110 | Weld Line Programming | D11 |
If every column in a table contains duplicate values, you cannot define a primary key with only one column. In this case, you can list two or more columns for the primary key. A key with more than one column is a composite key. The combination of column values should define a unique entity. If a composite key cannot be easily assigned, you may consider defining a new column that has unique values.
The following example shows a primary key containing more than one column;
it is a composite key.
Table 5. A Composite Primary Key on the EMP_ACT Table
| EMPNO (Primary Key) | PROJNO (Primary Key) | ACTNO (Primary Key) | EMPTIME | EMSTDATE (Primary Key) |
|---|---|---|---|---|
| 000250 | AD3112 | 60 | 1.0 | 1982-01-01 |
| 000250 | AD3112 | 60 | .5 | 1982-02-01 |
| 000250 | AD3112 | 70 | .5 | 1982-02-01 |
To identify candidate keys, select the smallest number of columns that define a unique entity. There may be more than one candidate key. In Table 19, there appear to be many candidate keys. The EMPNO column, the PHONENO, and the LASTNAME each uniquely identify the employee.
The criteria for selecting a primary key from a pool of candidate keys should be persistence, uniqueness, and stability of the key.
Of the three candidate keys in the example, only the employee number meets the above criteria. An employee may not have a phone number when joining a company. Last names can change, and, although they are unique at one point, are not always guaranteed to be so. Therefore, the employee number column is the better choice for the primary key. An employee is assigned a unique number only once, and that number is generally not updated as long as the employee remains with the company. Since each employee must have a number, the employee number column is persistent.