Progress
Database Design
Guide


Keys

There are two types of keys: primary and foreign. A primary key is a column (or group of columns) whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows. A good primary key has these characteristics:

NOTE: You can also have non-unique keys and word indexes.

A foreign key is a column value in one table that is required to match the column value of the primary key in another table. In other words, it is the reference by one table to another. If the foreign key value is not null, then the primary key value in the referenced table must exist. It is this relationship of a column in one table to a column in another table that provides the relational database with its ability to join tables. "Table Relationships and Normalization" describes this concept in more detail.

A composite key is a key composed of multiple columns.

Indexes

An index in a database operates like the index tab on a file folder. It points out one identifying column, such as a customer’s name, that makes it easier and faster to find the information you want.

When you use index tabs in a file folder, you use those pieces of information to organize your files. If you index by customer name, you organize your files alphabetically. If you index by customer number, you organize them numerically. Indexes in the database serve the same purpose.

You may use a single column to define a simple index, or a combination of columns to define a compound index. To decide which columns to use, you determine how the data in the table will be accessed. If users frequently look up customers by last name, then the last name is a candidate for an index. It is typical to base indexes on primary keys (columns that contain unique information).

An index has these advantages:


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095