A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table.
You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table.
A table can have only one PRIMARY KEY constraint, and no column that participates in the PRIMARY KEY constraint can accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity column.
When you specify a PRIMARY KEY constraint for a table, Microsoft® SQL Server™ enforces data uniqueness by creating unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.
If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
As shown, the au_id and title_id columns in the titleauthor table form a composite PRIMARY KEY constraint for the titleauthor table, which ensures that the combination of au_id and title_id is unique.
When you work with joins, PRIMARY KEY constraints relate one table to another. For example, to determine which authors have written which books, you can use a three-way join between the authors table, the titles table, and the titleauthor table. Because titleauthor contains columns for both the au_id and title_id columns, the titles table can be accessed by the relationship between titleauthor and titles.
Creating and Modifying PRIMARY KEY Constraints