Databases 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).
127
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 an 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, this 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)
;
