The CREATE INDEX Statement

Most systems have a command with syntax similar to this:

CREATE [UNIQUE] INDEX index_name
ON table_name (column_name)

The column and table names specify the column you want indexed and the table that contains it. To create an index on the au_id column of the authors table, the command is

create index auidind
on authors (au_id)
[index created]

It's a good idea to plan and create your indexes when you create your table, particularly if you need the indexes to guarantee uniqueness. However, SQL allows you to create indexes after there's data in a table, and this may be more efficient for some situations.

Most systems allow composite indexes (indexes involving more than one column) and unique indexes (indexes that prevent duplication of data). Another indexing option provided in some systems is a clustered index (one that sorts the table rows in the same physical order as the index). Check your system's reference manuals to see which types of indexes are allowed.

Composite Indexes

Composite indexes are used when two or more columns are best searched as a unit because of their logical relationship. For example, the authors table has a composite index on au_lname and au_fname.

The syntax for creating a composite index must specify all the columns in it. A command to create a composite index on the authors table might look something like this:

create index aunameind
on authors (au_lname, au_fname)
						

The composite index columns don't have to be in the same order as the columns in the CREATE TABLE statement. For example, the order of au_lname and au_fname could be reversed in the preceding index creation statement. For performance reasons, it's important to start with the name of the column you use most often in searches.

Unique Indexes

A unique index is one in which no two rows are permitted to have the same index value. The system checks for duplicate values when the index is created (if data already exists) and each time data is added. Unique indexes are usually created on the primary key column(s) in order to enforce their function as unique identifiers of the row.

Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, you would not want a unique index on a last_name column because there is likely to be more than one Smith or Wong in tables of even a few hundred rows. On the other hand, a unique index on a column holding Social Security numbers would be a good idea. Uniqueness is a characteristic of the data: Each person has a different Social Security number. Furthermore, a unique index serves as an integrity check. A duplicate Social Security number reflects some kind of error in data entry or an error on the part of the government.

SQL implementations that support unique indexes must have some way of enforcing that uniqueness. Usually the system guarantees uniqueness by rejecting commands that would

• Create a unique index on existing data that includes duplicate values

• Change data on which there is a unique index in such a way that duplicates would be introduced

You can use the UNIQUE keyword on composite indexes as well as on single-column indexes.