So one can run, for example:
ALTER TABLE nice_table DROP INDEX IF EXISTS ugly_index; DROP INDEX IF EXISTS ugly_index ON nice_table;
However, there is no such thing as
DROP IF EXISTS PRIMARY KEY. Why? I think that we all can easily guess: a primary key is too important to be unsure if it exists or not. That’s a reasonable assumption… in theory.
In practice, all limitations are, at least in rare cases, not so reasonable. Yes, today I had to write a procedure that drops the PK if it exists, knowing that sometimes it doesn’t. Explaining the logic behing this weird behavior requires space and time (that seem to be the same thing, if we trust modern physics). I’ll just say that I need to do complex normalization tasks – data from 1 table to 9 tables… really. In the process I need to accept duplicates, and later remove them.
So, can we easily drop/create a PK
IF [NOT] EXISTS? Yes, because from a DDL’s point of view, a PK is just an index called
DROP INDEX IF EXISTS `PRIMARY`;