SQL > ALTER TABLE > Add Index Syntax

Sometimes after a table has been created in a database, we find that it is advantageous to add an index to that table to speed up queries involving this table (to understand what an index is and how it can help speed up SQL queries, please see the INDEX section). To do this in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD INDEX command to tell the RDBMS that we want to add an index.

The syntax for adding an index to an existing table is,

ALTER TABLE "table_name"
ADD INDEX "index_name" (column_name);

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

Assume we want to add an index on the "Country" column. To do this, we type in the following:

ALTER TABLE Customer ADD INDEX IDX_COUNTRY (Country);

Please note that using ALTER TABLE to add an index is supported in MySQL but not in Oracle or SQL Server.

Next: SQL DROP INDEX

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact