O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

DROP INDEX

Synopsis

The DROP INDEX command destroys one or more indexes within the current database. When an index is dropped, all the space it previously consumed is immediately regained. DROP INDEX does not, however, destroy PRIMARY KEY or UNIQUE constraints, which must be done with the ALTER TABLE . . . DROP command. Refer to the CREATE TABLE command for more information about primary key and unique constraints.

Vendor

Command

SQL Server

Supported, with variations

MySQL

Supported, with variations

Oracle

Supported, with variations

PostgreSQL

Supported, with variations

SQL99 Syntax and Description

DROP INDEX table_name.index_name

PostgreSQL follows the SQL99 standard, with variations.

Microsoft SQL Server Syntax and Variations

DROP INDEX {table_name | view_name}.index_name [,...n]

Microsoft SQL Server allows indexes created on both tables and views to be dropped. If a clustered index on a table that contains nonclustered indexes is dropped, all nonclustered indexes are rebuilt and assigned new pointers.

MySQL Syntax and Variations

DROP INDEX table_name.index_name [,...n]

Older versions of MySQL include this command only for compatibility reasons; however, newer versions actually destroy the specified index. The statement is functionally equivalent to the MySQL statement ALTER TABLE . . . DROP INDEX.

MySQL allows multiple indexes to be dropped by separating each table and index name with a comma.

Oracle Syntax and Variations

DROP INDEX [owner_name.]index_name

Oracle allows indexes ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required