A key is a set of columns that can be used to identify or access a particular row or rows. The key is identified in the description of a table, index, or referential constraint. The same column can be part of more than one key.
A unique key is a key that is constrained so that no two of its values are equal. The columns of a unique key cannot contain NULL values. 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 mechanism used to enforce the uniqueness of the key is called a unique index. The unique index of a table is a column, or an ordered collection of columns, for which each value identifies (functionally determines) a unique row. A unique index can contain NULL values.
The primary key is one of the unique keys defined on a table, but is selected to be the key of first importance. There can be only 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. (You can also define indexes on non-primary key columns to efficiently access data when processing queries.)
If a table does not have a "natural" unique key, or if arrival sequence is the method used to distinguish unique rows, using a time stamp as part of the key can be helpful. (See also Defining Identity Columns.)
Primary keys for some of the sample tables are:
The following example shows part of the PROJECT table, including its
primary key column.
Table 6. 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. 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 defined, you may consider creating a new column that has unique values.
The following example shows a primary key containing more than one column
(a composite key):
Table 7. 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 2, there appear to be many candidate keys. The EMPNO, the PHONENO, and the LASTNAME columns 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 three candidate keys in the example, only EMPNO satisfies all of these criteria. An employee may not have a phone number when joining a company. Last names can change, and, although they may be unique at one point, are not guaranteed to be so. The employee number column is the best 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, values in the employee number column are persistent.
An identity column provides a way for DB2 to automatically generate a unique numeric value for each row in a table. A table can have a single column that is defined with the identity attribute. Examples of an identity column include order number, employee number, stock number, and incident number.
Values for an identity column can be generated always or by default.
Identity columns are ideally suited to the task of generating unique primary key values. Applications can use identity columns to avoid the concurrency and performance problems that can result when an application generates its own unique counter outside of the database. For example, one common application-level implementation is to maintain a 1-row table containing a counter. Each transaction locks this table, increments the number, and then commits; that is, only one transaction at a time can increment the counter. In contrast, if the counter is maintained through an identity column, much higher levels of concurrency can be achieved because the counter is not locked by transactions. One uncommitted transaction that has incremented the counter will not prevent subsequent transactions from also incrementing the counter.
The counter for the identity column is incremented (or decremented) independently of the transaction. If a given transaction increments an identity counter two times, that transaction may see a gap in the two numbers that are generated because there may be other transactions concurrently incrementing the same identity counter (that is, inserting rows into the same table). If an application must have a consecutive range of numbers, that application should take an exclusive lock on the table that has the identity column. This decision must be weighed against the resulting loss of concurrency. Furthermore, it is possible that a given identity column can appear to have generated gaps in the number, because a transaction that generated a value for the identity column has rolled back, or the database that has cached a range of values has been deactivated before all of the cached values were assigned.
The sequential numbers that are generated by the identity column have the following additional properties: