Database Reference
In-Depth Information
This CREATE INDEX command creates an index named CustomerName. The index is for the Customer
table, and the index key is the CustomerName field. In this example, the index name is the same as the
index key. This format is not a requirement, but it is a good general practice.
Figure 4-13 shows the creation of an index on the CustomerName field in the Customer table using
Access. As illustrated in the figure, there are three choices for index options: No, Yes (Duplicates OK), and
Yes (No Duplicates).
129
Index key
Options for not creating an
index, creating an index that allows
duplicates, and creating an index
that prohibits duplicates
FIGURE 4-13
Creating index on a single field in Access
The first Indexed option, No, is the default. You select No when you need to remove a previously created
index. You select Yes (Duplicates OK) to create an index that allows duplicate values. In this case, Access
allows more than one customer with the same name. When you select Yes (No Duplicates), Access creates
the index, but you cannot add a customer with the same name as an existing customer in the database. The
third option is used to enforce uniqueness when it is appropriate. For example, the third option would be a
good choice for a Social Security number field.
When you create an index whose key is a single field, you have created a single-field index (also called a
single-column index). A multiple-field index (also called a multiple-column index) is an index with more
than one key field. When creating a multiple-field index, you list the more important key first. In addition, if
data for either key appears in descending order, you must follow the field name with the word DESC.
To create an index named RepBal with the keys RepNum and Balance and with the balances listed in
descending order, you could use the following SQL command:
CREATE INDEX RepBal
ON Customer (RepNum, Balance DESC)
;
