Each database table must have a field or a combination of fields that holds a value that uniquely identifies each record. For example, a customer number would uniquely identify each customer. This field or fields are called the primary key of the table. The primary key serves several purposes in a RDBMS. Because the value is unique, it ensures there are no duplicate records in the database. The primary key is also used to establish table relationships and, thereby, connect the data in related records held in different tables. Records are stored in order by the primary key.
In order to be a primary key, the field must never be null. Null means there is a missing or unknown value. A null value is not the same as a zero or a blank. In a numeric field, a zero may be a real value. Another characteristic of a primary key field is that the values in that field are rarely (ideally never) changed.
In trying to identify what the primary key should be for a table, first look for a single field that will hold a value that is unique for each record. For example, in a table holding information on orders placed with a company, there would be a field with a unique number for each order.
If there is no single field that meets the qualifications for a primary key, you can have a multi-field, compound, or composite primary key - a combination of fields that uniquely identifies each record (and will have no nulls and has values that won't change). You want to use the fewest fields possible that satisfy these criteria. Following is an example of a table with a multi-field primary key-the fields included in the primary key are indicated by an asterisk.
| *Room | *Date | *StartTime | *EndTime | *Purpose |
|---|---|---|---|---|
| A | 2/20/01 | 1:00 PM | 3:00 PM | Staff Meeting |
| A | 2/20/01 | 4:00 PM | 5:00 PM | Dept. Managers |
| B | 2/01/01 | 9:00 AM | 10:30 AM | STAR Project |
| B | 2/20/01 | 1:00 PM | 5:00 PM | Proposal Presentation |
| C | 2/10/01 | 9:00 AM | 10:00 AM | Staff Meeting |
Above, an example of a table with a multi-field primary key
If your data does not currently include a unique identifier and if combining fields starts to get too unwieldy, you should consider establishing a new field to hold some kind of unique number. It is inadvisable to try to build in a coding scheme to this identifier. For example, if a customer number included a code that represented the customer name, you would have to change the code if the name changed. Some RDBMS's have the capability and a data type to generate a unique number for you.
Some tables may have multiple possibilities for the primary key. Each of these is called a candidate key. When selecting a primary key from among the candidate keys, choose the one that contains the least number of fields, and secondarily, is most closely associated with the subject of the table.
To summarize, the characteristics of a primary key field or fields are that its values:
In most RDBMS's, the records in a table are automatically in order by the values in the primary key. But you don't care what order the records are in! Why don't you care? Because, with a unique identifier, you'll always be able to find the record you need! Besides, you can sort the records any way you want, any time you want.