SQL INDEX Constraint

The INDEX is used to create and retrieve data from the database very fast.

An Index can be created using a single or group of columns in a table.

To create an Index in tables, you can use the CREATE INDEX statement.

Note: Be sure to create indexes only on columns that will be regularly searched against because updating a table with indexes demands more time than updating a table without indexes.

CREATE INDEX Syntax

The syntax to create an index on a table where duplicate values are allowed can be given as follows:

CREATE INDEX index_name 
    ON table_name (column1, column2, ... columnN);

CREATE UNIQUE INDEX Syntax

The syntax to create an index on a table where duplicate values are not allowed can be given as follows:

CREATE UNIQUE INDEX index_name
    ON table_name(column1, column2 ... columnN);

CREATE INDEX Example

The following SQL statement will create an index named "first_name_idx" on the "first_name" column in the "Employee" table:

CREATE INDEX first_name_idx 
    ON employee (first_name);

If you need to create an index on a collection of columns, you can see the following example that will create an index named "name_idx" on the combination of the "first_name" and "last_name" columns in the "Employee" table.

CREATE INDEX name_idx
    ON employee (first_name, last_name);

DROP INDEX Statement

You can drop an Index using the DROP INDEX statement.

The syntax to drop Index in different database systems can be given as follows:

• For MySQL, MariaDB :

  ALTER TABLE table_name
    DROP INDEX index_name;
  

• For Oracle :

  DROP INDEX index_name;
  

• For SQL Server :

  DROP INDEX table_name.index_name;