Chapter 3 Building Physical Diagrams

Defining keys in a physical diagram

A key is a column, or a combination of columns, that uniquely identifies a row in a table. Each key can generate a unique index or a unique constraint in a target database.

The physical diagram supports the following types of keys:

Key	Description
Primary	Column or combination of columns whose values uniquely identify every row in a table. A table can have only one primary key
Alternate	Column or combination of columns (not the same column or combination of columns as for a primary key) whose values uniquely identify every row in a table
Foreign	Column or combination of columns whose values are required to match a primary key, or alternate key, in some other table

Example

The TITLE table shown below has a primary, alternate and foreign key:

TITLE_ID is the primary key and consists of the column TITLE ISBN which identifies each book title in the table
TITLE_NAME is an alternate key containing the columns TITLE NAME and TITLE TYPE. It allows each title to be identified by its name and type, The fact that it is an alternate key indicates that there is a constraint that no two titles of the same type can have the same name

The TITLE table also contains the foreign key column PUBLISHER ID. This column references the primary key column in the Publisher table.