A primary key is a central tool for identifying records reliably in a relational database. This lesson explains what makes an attribute set a primary key, how natural and surrogate keys differ, how primary keys are designated in SQL, and how alternate keys relate to the chosen primary key.
A primary key is a designated attribute, or set of attributes, that uniquely identifies each row in a table. In relational terminology, a row is also called a tuple, and a table is called a relation. No two rows in the same table may have the same primary-key value.
All flashcards
What is a primary key?
A designated minimal set of one or more attributes that uniquely identifies each row in a table.
How does a natural key differ from a surrogate key?
A natural key uses existing meaningful data as an identifier, while a surrogate key is created specifically to identify records.
What is a candidate key?
A minimal set of attributes that uniquely identifies each row. One candidate key may be selected as the table's primary key.
What must be true of SQL primary-key values?
They must uniquely identify rows and cannot be null. A primary key can consist of one column or multiple columns.
What is an alternate key, and how can SQL protect it?
An alternate key is a candidate key not selected as the primary key. A UNIQUE constraint can prevent duplicate values for it.
Quiz questions
What is the primary purpose of a primary key?Answer: To uniquely identify each row in a table.
A timestamp and location together uniquely identify an event, but neither identifies it alone. What kind of key could they form?Answer: A composite candidate key.
Why might a database designer choose a surrogate key instead of a natural key?Answer: The natural identifier may require several columns or cumbersome values.
A table has two candidate keys, and one is selected as its primary key. What is the other candidate key called?Answer: An alternate key.
In the relational model, what theoretical distinction does the primary-key designation give one candidate key over the others?Answer: None; the model treats keys as keys rather than giving the primary one a special theoretical role.
Based on the Wikipedia article Primary key, licensed CC BY-SA 4.0. Adapted into a summary, flashcards and quizzes.