Relational Databases - Java Database Programming Bible - page 8

between tables rely on using the primary key as a linking mechanism, as discussed in
Chapter 2 .

Primary keys can be simple or composite . A simple key is a key made up of one
column, whereas a composite key is made up of two or more columns. Although there
is no absolute rule as to how you select a column or group of columns for use as a
primary key, the decision should usually be based upon common sense. In other
words, you should base your choice of a primary key upon the following factors:

Use the smallest number columns necessary, to make key access efficient.

Use columns or groups of columns that are unlikely to change, since changes will break links
between tables.

Use columns or groups of columns that are both simple and understandable to users.

In practice, the most common type of key is a column of unique integers specifically
created for use as the primary key. The unique integer serves as a row identifier or ID
for each row in the table. Oracle, in fact, defines a special ROW_ID pseudo column,
and Access has an AutoNumber data type commonly used for this purpose. You can
see how this works in Table 1-2 .

Another good reason to use a unique integer as a primary key is that integer
comparisons are far more efficient than string comparisons. This means that
accessing data using a single integer as a key is faster than using a string or, in the
case of a multiple column key, several integers or strings.

Since primary keys are used as unique row identifiers, they can never
have a NULL value. The NOT NULL integrity constraint must be applied to
a column designated as a primary key. Many Relational database
Management Systems apply the NOT NULL constraint to primary keys
automatically.

Note

Foreign Keys

A foreign key is a column in a table used to refe rence a primary key in another table.
If your database contains only one table, or a number of unrelated tables, you won't
have much use for your primary key. The primary key becomes important when you
need to work with multiple tables. For example, in addition to the Customers Table
( Table 1-2 ), your business application would probably include an Inventory Table, an
Orders Table, and an Ordered Items Table. The Inventory Table is shown in Table
1-4 .

Table 1-4: Inventory Table

Item_Number Name Description Qty Cost

1001 Corn Flakes Cereal 178 1.95