Primary Key

See CandidateKey.

A non-empty set of columns (an ordered set in some RDBMS products) that contain non-null values and whose values uniquely identify a row (record). A table cannot have more than one PrimaryKey, and should not have zero primary keys (this is usually but not always enforced in RDBMS systems).

Often the term PrimaryKey is used to refer to a set of columns which is a strict subset of all of the columns in the table; and in many cases it is assumed that the PrimaryKey consists of a single column.

Primary keys are useful and necessary because:

• They uniquely identify the record
• They are needed to implement ForeignKeys (though any CandidateKey will suffice).

The ideal primary key would be

• a CandidateKey
• mandatory - it is never possible to not have one (do all employees have SSN's?)
• immutable - can never be updated
• and minimal - as small as possible

Primary keys are distinguished from other CandidateKeys? in that their values are used by other tables to refer to rows in the (primary key's) table. A primary key also may capture the notion of ObjectIdentity (e.g., I'm still the same person even though my name, weight, age, first and last name have changed). In a normalized database, primary key values are the only values that should appear multiple times in the database.

Whenever a row is inserted into a table, you need to provide (or generate) a primary key for it. Uh, no you don't... the entire record could be a CandidateKey, in which case it comes for free. KeyGeneration? is done when there is no column or set of columns which meets the uniqueness constraint, account numbers are typical examples of generated PrimaryKeys. See PortablePrimaryKeyGeneration.

Is it really necessary for the set of columns defining a primary key to be ordered? I can't think of any reasons why other than efficiency; I was under the impression that the relative ordering of columns _specifically_ didn't matter under relational algebra.? -- WilliamUnderwood

• In the RelationalModel, it doesn't. Many RDBMS products aren't that faithful.

---

CategoryDatabase RelationalModel