The DROP INDEX command can be used to delete a non-primary key index. However, this method cannot delete indexes created as a result of a PRIMARY KEY or UNIQUE constraint. The constraint must be removed instead. Use ALTER TABLE with the DROP CONSTRAINT clause to remove the constraint and corresponding index.
The syntax shown below is common in SQL products such as MySQL, SQL Server, Oracle SQL*Plus, and others.
DROP INDEX index_name ON table_name;
Also, MySQL does allow the following command to drop the index.
ALTER TABLE table_name DROP INDEX index_name;
The below command will help in removing the indexes that are associated with the constraints.
ALTER TABLE tbStudents DROP CONSTRAINT PK_Student_StudentID
WITH (ONLINE = ON);
Hope you find this article helpful.