CODEWITHSUNDEEP
mysqldatabase
tiranaghoul
tiranaghoul

Reputation: 21

Is there any way to check if an index exists before you drop it?

I get the following error:

Database error 1091 while doing query Can't DROP 'indexname'; check that column/key exists

Is there any way to check if an index exists before you drop it without using stored procedures?

Upvotes: 1

Views: 3875

Answers (2)

ionyekanna
ionyekanna

Reputation: 440

This worked for me

DROP INDEX IF EXISTS unique_name ON mytable;

alter table mytable add unique unique_name (....)

unique_name is the constraints name and mytable is the table its applied on.

Hope this helps

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

Try by using SHOW INDEX

SHOW INDEX FROM Your_table

It will help to know index name of any column

SELECT * FROM information_schema.statistics 
  WHERE table_schema = 'your_databasename'
    AND table_name = 'your_tablename' AND column_name = 'column_name'

For more details

Upvotes: 1

Related Questions