DDL - CREATE INDEX

Used to create an index on an existing table. The ALTER TABLE statement can also be used to create (or drop) an index on a table.  The syntax for this command varies across systems.

Uses (apart from speeding up searches in large tables and in multitable queries):

– PRIMARY uniquely identifies the row (UNIQUE and NOT NULL by definition)

– UNIQUE prevents entry of duplicate values

– DISALLOW NULLS prevents null values in the indexed field

Exercise: Create the missing primary key on the SupplierID column in table MySuppliers.

1. Type the following SQL statement in the SQL design window:

CREATE INDEX MySuppPK

ON MySuppliers (SupplierID)

WITH PRIMARY;

2. Execute the statement.

3. Save the query as DefineSupplierPK and close it.

4. Open the table MySuppliers in Design view to check the result.

Syntax

CREATE [ UNIQUE ] INDEX indexname

ON tablename (column1 [ASC|DESC], column2 [ASC|DESC], ...)

[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]