A table often 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. The primary key cannot be null.
You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table (see "Creating Tables" and "Altering Tables," later in this chapter).
As shown here, the au_id and title_id columns in the titleauthor table together form a composite primary key for the titleauthor table.
You can define a PRIMARY KEY constraint for a table, and the constraint will automatically create an index. This index enforces uniqueness in the table and permits fast access to data when you use the primary key in queries.
A foreign key is a column or combination of columns whose values match the primary key of another table. A foreign key doesn't have to be unique. However, foreign key values must be copies of the primary key values: no value in the foreign key except NULL can exist unless the same value exists in the primary key. A foreign key can be NULL, but if any part of a composite foreign key is NULL, the entire foreign key must be NULL. You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table see Creating Tables and Altering Tables.
You can join tables by using their primary and foreign keys. For example, in the pubs database the title_id column is the primary key of the titles table and the foreign key of the titleauthor, sales, and roysched tables. Accordingly, you can use title_id to join the titles table with the titleauthor, sales, or roysched tables. For details on joins, see Retrieving Data with Queries.