Drop Primary Key if exists in MariaDB

MariaDB improved the use of IF EXISTS and IF NOT EXISTS clauses in two ways: they added them to almost all DDL statements (along with OR REPLACE), and added them to several ALTER TABLE sub-commands.

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 `PRIMARY`. So:

DROP INDEX IF EXISTS `PRIMARY`;

Enjoy!
Federico

Advertisements