SQL CREATE INDEX

-- create table
CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);

-- create index
CREATE INDEX college_index
ON Colleges(college_code);

SQL CREATE INDEX Syntax

The syntax of the SQL CREATE INDEX statement is:

CREATE INDEX index_name
ON table_name (column_name1, column_name2, ...);

Here,

• index_name is the name given to the index
• table_name is the name of the table on which the index is to be created
• column_name1, column_name2, ... are the names of the columns to be included in the index

CREATE UNIQUE INDEX for Unique Values

If you want to create indexes for unique values in a column, we use the CREATE UNIQUE INDEX constraint. For example,

-- create table
CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);

-- create unique index
CREATE UNIQUE INDEX college_index
ON Colleges(college_code);

Here, the SQL command creates a unique index named college_index on the Colleges table using the college_code column.

Note: Although the index is created for only unique values, the original data in the table remains unaltered.

Remove Index From Tables

To remove INDEX from a table, we can use the DROP INDEX command. For example,

SQL Server

DROP INDEX Colleges.college_index;

PostgreSQL, Oracle

DROP INDEX college_index;

MySQL

ALTER TABLE Colleges
DROP INDEX college_index;

Here, the SQL command removes an index named college_index from the Colleges table.

Note: Deleting an index in SQL means only the index is deleted. The data in the original table remains unaltered.

Recommended Reading: SQL Constraints