|
||
|
Lesson 11
Objective
|
Primary keys Define primary keys. |
|
|
As noted in the preceding lesson, tables consist of
candidate key: A column or set of columns that could can used as a primary key for a table.
Key columns contain information that distinguishes that person, place, or thing from every other item represented in the table. A key column (or set of key columns) that distinguishes a record from every other record in a table is referred to as a candidate key. A car's license-plate number is one example of a candidate key. Within a given state, only one car can have a particular license number, so a table describing CAR might contain a column labeled PlateNo. In a nationwide database of license-plate numbers, where the same license plate number could be assigned to cars in different states, the state and PlateNo key columns would form a candidate key. The problem with using a license-plate number to identify a car is that the car's owner can request new plates with a chosen message or the state may change their numbering system to accommodate more cars. In either case, the license-plate number associated with the car would change. Changing key values in a table can cause a great deal of confusion, especially if an update goes awry and the wrong value is assigned to an item. So, when you design a table, look for candidate keys with two properties:
If the candidate key meets these two additional requirements, it can be considered a primary key. Guidelines for Primary Keys
A primary key should never be a value that is also used outside the database, such as a Social Security number (Social Insurance
Number in Canada) or license-plate number. Social Security numbers are especially bad choices because they can be recycled,
incorrectly assigned, or changed. More important, the U.S. government strictly limits when Social Security numbers can be collected
and used. It's much better to create a unique identifier to represent each row in a table and not try to store
meaningful data in the primary-key column.
Advice on primary key fields
Most database designers mark primary-key fields in an ER diagram with an asterisk (*).
Some RDBMSs underline the key fields or write the fields' name in bold-face type. The next lesson defines concatenated primary keys . concatenated primary key: A primary key made up of more than one field. |
||
|
||