Primary Key

Source: TechRepublic: The Great Primary Key Debate


Primary key defined

Before we enter the debate, we define the term primary key. A primary key uniquely identifies each record within a table, but that’s only half the story. The main purpose of a primary key is to relate records to additional data stored in other tables. In this sense, the primary key is a simple pointer between related records in different tables. As such, the primary-key value has no meaning to the user and no association to the data to which it’s assigned.

The developer must apply a few rules when choosing a primary key for each table:

* The primary key must uniquely identify each record.
* A record’s primary-key value can’t be null.
* The primary key-value must exist when the record is created.
* The primary key must remain stable—you can’t change the primary-key field(s).
* The primary key must be compact and contain the fewest possible attributes.
* The primary-key value can’t be changed.

Note that the word must in the above list doesn’t mean perhaps or most of the time—must is absolute. That doesn’t mean you can’t break the rule; it just means your application won’t adhere to the relational database model if you choose to break these established rules. In addition, even though relational database theory requires a particular condition, don’t expect the relational database system to enforce it as you’re developing your application—that’s up to you. For more complete information on the relational database model, normalization, and primary keys, visit the following Web sites:

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License