Sql 简明教程

SQL - Drop Index

SQL 中的 DROP 语句用于删除或删除现有的数据库对象，例如表、索引、视图或过程。每当我们在任何数据库对象中使用 DROP 语句时，它将连同它们的相关数据一起永久删除它们。

The DROP statement in SQL is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, it will remove them permanently along with their associated data.

而当该数据库对象为索引时，将使用 SQL 中的 DROP INDEX 语句。

And when that database object is an index, the DROP INDEX statement in SQL is used.

Dropping an SQL Index

可以使用 DROP INDEX 语句从数据库表中删除 SQL 索引。

An SQL Index can be dropped from a database table using the DROP INDEX statement.

Note - 我们无法删除由 PRIMARY KEY 或 UNIQUE 约束创建的索引。为了删除它们，您需要使用 ALTER TABLE 语句完全删除约束。

Note − We cannot delete the indexes created by PRIMARY KEY or UNIQUE constraints. In order to delete them, you need to drop the constraints entirely using ALTER TABLE statement.

Syntax

以下是 SQL 中 DROP INDEX 命令的语法：

Following is the syntax of the DROP INDEX command in SQL −

DROP INDEX index_name ON table_name;

在此，

Here,

index_name is the name of the index that you want to drop.
table_name is the name of the table that the index is associated with.

Example

在此示例中，我们将了解如何删除名为 CUSTOMERS 的表上的索引，可以使用以下查询创建该索引：

In this example, we will learn how to drop an index on a table named CUSTOMERS, which can be created using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

现在，使用以下查询将一些值插入到上述创建的表中：

Now, insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2000),
(4, 'Chaitali', '25', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'Hyderabad', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

在创建表后，使用以下查询在 CUSTOMERS 表中的列 NAME 上创建索引：

Once the table is created, create an index on the column NAME in the CUSTOMERS table using the following query −

CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

现在，使用以下 SHOW INDEX 查询验证 CUSTOMERS 表上是否创建了索引：

Now, verify if the index is created on the CUSTOMERS table using the following SHOW INDEX query −

SHOW INDEX FROM CUSTOMERS;

在执行上述查询时，索引列表显示如下：

On executing the above query, the index list is displayed as follows −

Table

Non_unique

Key_name

Seq_in_index

Column_name

customers

PRIMARY

customers

index_name

NAME

然后，使用以下 DROP INDEX 语句删除 CUSTOMERS 表中的相同索引 INDEX_NAME：

Then, drop the same index INDEX_NAME in the CUSTOMERS table using the following DROP INDEX statement −

DROP INDEX INDEX_NAME ON CUSTOMERS;

Output

如果我们编译并运行上述查询，则结果将生成如下：

If we compile and run the above query, the result is produced as follows −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

使用以下查询验证是否已删除了列 NAME 的索引：

Verify if the index for the column NAME is dropped using the following query −

SHOW INDEX FROM CUSTOMERS;

在以下索引列表中，你可以看到列 Name 的名称缺失。

In the following list of indexes, you can observe that name of the column Name is missing.

Table

Non_unique

Key_name

Seq_in_index

Column_name

customers

PRIMARY

DROP INDEX with IF EXISTS

SQL 中的 DROP INDEX IF EXISTS 语句仅在表中存在索引时才用于删除索引。当你要删除索引但又不确定索引是否存在时，此语句特别有用。MySQL not 支持此子句。

The DROP INDEX IF EXISTS statement in SQL is used to drop an index only if it exists in the table. This statement is specifically useful when you want to drop an index, but you are not sure if the index exists. This clause is not supported by MySQL.

IF EXISTS 子句确保此语句仅在索引存在时才删除索引。如果索引不存在，它只终止执行。

The IF EXISTS clause ensures that the statement only removes the index if it exists. If the index does not exist, it simply terminates the execution.

Syntax

以下是 SQL 中 DROP INDEX IF EXISTS 的语法：

Following is the syntax of the DROP INDEX IF EXISTS in SQL −

DROP INDEX IF EXISTS index_name
ON table_name;

在此，

Here,

index_name is the name of the index that you want to drop.
table_name is the name of the table that the index is associated with.

Example

在此示例中，让我们尝试删除 SQL Server 数据库中的索引。

In this example, let us try to drop an index in the SQL Server database.

让我们考虑之前创建的表 CUSTOMERS，并使用以下查询为表中的 NAME 列创建索引：

Let us consider the previously created table CUSTOMERS and let us create an index for the NAME column in the table using the following query −

CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

然后，让我们使用以下查询删除它：

Then, let us drop it using the following query −

DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

让我们使用以下查询验证 NAME 的索引是否已删除：

Let’s verify whether the index for the NAME is dropped or not using the following query −

EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

正如您所观察到的，NAME 列已从索引列表中删除。

As you observe, the column NAME is deleted from the list of indexes.

index_name

index_description

index_keys

PK_CUSTOMER_3214EC27CB063BB7

clustered, unique, primary key locatedPRIMARY on PRIMARY

Example

现在，让我们使用以下查询删除 CUSTOMERS 表中的索引 that doesn’t exist ：

Now, let us delete an index that doesn’t exist in the CUSTOMERS table using the following query −

DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Output

由于数据库中不存在具有指定名称的索引，因此上面的查询在不给出任何错误的情况下直接终止执行。

Since no indexes with the specified name exist in the database, so the above query simply terminates the execution without giving any error.

Commands completed successfully.

Removing indexes created by PRIMARY KEY or UNIQUE

DROP INDEX 语句不会删除由 PRIMARY KEY 或 UNIQUE 约束创建的索引。要删除与它们关联的索引，我们需要完全删除这些约束。这是通过 ALTER TABLE… DROP CONSTRAINT 语句完成的。

The DROP INDEX statement does not drop indexes created by PRIMARY KEY or UNIQUE constraints. To drop indexes associated with them, we need to drop these constraints entirely. And it is done using the ALTER TABLE… DROP CONSTRAINT statement.

Syntax

以下是在 SQL 中 ALTER TABLE… DROP CONSTRAINT 语句的语法：

Following is the syntax of the ALTER TABLE… DROP CONSTRAINT statement in SQL −

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

在此，

Here,

table_name is the name of the table that contains the PRIMARY KEY constraint.
constraint_name is the name of the PRIMARY KEY constraint that you want to drop.

Example

假设先前创建的表（CUSTOMERS）并让我们首先使用以下查询列出在表上创建的所有索引：

Assume the previously created table (CUSTOMERS) and let us first list all the indexes that are created on the table using the following query −

EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

列表显示如下：

The list is displayed as follows −

index_name

index_description

index_keys

PK_CUSTOMER_3214EC27CB063BB7

nonclustered located on PRIMARYID

这里， PK_CUSTOMER_3214EC27CB063BB7 是创建在 CUSTOMERS 表的 ID 列上的 PRIMARY KEY 约束的名称。

Here, the PK_CUSTOMER_3214EC27CB063BB7 is the name of the PRIMARY KEY constraint that was created on the ID column of the CUSTOMERS table.

现在，让我们删除由 PRIMARY KEY 约束创建的索引。

Now, let us drop the index created by the PRIMARY KEY constraint.

ALTER TABLE customers
DROP CONSTRAINT PK__CUSTOMER__3214EC27CB063BB7;

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

通过使用以下查询列出现有索引验证它是否已删除：

Verify whether it is dropped or not by listing the existing indexes using the following query −

EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

将显示以下错误，因为索引列表为空。

The following error is displayed because the list of indexes is empty.

The object 'CUSTOMERS' does not have any indexes, or you do not have permissions.