CREATE INDEX Statement

Show All

Hide All

Creates a new index on an existing table.

 Note   For non-Microsoft Access databases, the Microsoft Access database engine does not support the use of CREATE INDEX (except to create a pseudo index (pseudo index: A dynamic cross-reference of one or more table data fields (columns) that permits an ODBC table (server table) without a unique index to be edited.) on an ODBC (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.) linked table (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.)) or any of the data definition language (Data Definition Language (DDL): The language used to describe database attributes, especially tables, fields, indexes, and storage strategy. ANSI defines this to have the tokens CREATE, DROP, and ALTER. DDL is a subset of structured query language (SQL).) statements. Use the DAO (Data Access Objects (DAO): A data access interface that communicates with Microsoft Jet and ODBC-compliant data sources to connect to, retrieve, manipulate, and update data and the database structure.) Create methods instead. For more information see the Remarks section.

Syntax

CREATE [ UNIQUE ] INDEX index
ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

The CREATE INDEX statement has these parts:

Remarks

To prohibit duplicate values in the indexed field or fields of different records, use the UNIQUE reserved word.

In the optional WITH clause you can enforce data validation rules. You can:

• Prohibit Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) entries in the indexed field or fields of new records by using the DISALLOW NULL option.
• Prevent records with Null values in the indexed field or fields from being included in the index by using the IGNORE NULL option.
• Designate the indexed field or fields as the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) by using the PRIMARY reserved word. This implies that the key is unique, so you can omit the UNIQUE reserved word.

You can use CREATE INDEX to create a pseudo index on a linked table (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.) in an ODBC data source (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.), such as Microsoft® SQL Server™, that does not already have an index. You do not need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. Creating a pseudo-index on a table that would ordinarily be read-only can be especially useful.

You can also use the ALTER TABLE statement to add a single- or multiple-field index to a table, and you can use the ALTER TABLE statement or the DROP statement to remove an index created with ALTER TABLE or CREATE INDEX.

 Note   Do not use the PRIMARY reserved word when you create a new index on a table that already has a primary key; if you do, an error occurs.