A primary key is a column, or a set of columns, whose values identify each row in a table. Think of CategoryID as the number on a labeled folder: every category needs its own number so another table can point to exactly the right category. Replacing repeated names with key values reduces repeated data and creates table relationships. section 8.1.2 section 18.1.1
A primary-key value must be unique and present for every row. It also must not change: other tables may use that value to refer to the row. These rules preserve referential integrity, so a reference continues to mean the same row even when descriptive data, such as a category name, changes. section 18.3.1
Add a key to a category table
Give category.CategoryID a named PRIMARY KEY constraint. Naming it PK_category makes its purpose clear and makes later changes or deletion easier. Usually define the key while creating a table; ALTER TABLE also adds one to an existing table. section 18.3.2 section 19.1
A key can use more than one column when the combination, rather than either column alone, identifies a row. Before adding such a constraint, check existing data: duplicate key combinations prevent creation. Locate the duplicate rows, correct the inconsistent value where appropriate, then add the constraint. section 18.3.2 section 18.7
CREATE TABLE category (
CategoryID int,
CategoryName varchar(20),
CONSTRAINT PK_category PRIMARY KEY (CategoryID)
);
This complete CREATE TABLE example defines CategoryID and CategoryName, then adds the clearly named PK_category primary-key constraint. section 18.3.2
Primary keys support joins and views. When a view needs to join to other tables, include primary-key values from its underlying tables where possible instead of relying on descriptive names or foreign-key values. section 20.1.3
