Mysql : Drop a unique key if exists -- but without knowing name of the key or whether it exists

Question

Don't ask why (as the answer is 'our totally weird and bepsoke setup...'), but I need to drop a unique key on a table (if it exists) without knowing the name of the key -- only the columns that make up the key.

e.g. I have this table

    CREATE TABLE `my_table` (
                             `id` binary(36) NOT NULL,
                             `username` char(12) NOT NULL DEFAULT '',
                             `password` char(32) NOT NULL DEFAULT '',
                             `role` char(1) NOT NULL,
                             PRIMARY KEY (`id`),
                             UNIQUE KEY `username_2` (`username`,`role`),
                             UNIQUE KEY `username` (`username`),
                             UNIQUE KEY `username_3` (`username`),
                             UNIQUE KEY `username_4` (`username`),
                             )

and I'd like to drop the (username,role) key but not the others (although only if it exists! - by the time I get to it, it might already have been dropped!)

Now I know how to drop a key when I know the constraint name

ALTER TABLE `my_table` DROP KEY `name_of_my_key`

and I know how to check if a unique key exists

SELECT EXISTS (SELECT constraint_name
                 FROM INFORMATION_SCHEMA.table_constraints
                WHERE table_name = 'my_table' AND constraint_type='UNIQUE');

But this still doesn't help me when I only know the columns that make up the unique key.

Appreciate your help... Once I know a key exists, how can I derive the constraint_name(s) from only knowing which columns make up the constraint to allow me to drop them? Notice constraint name(s) because mySQL allows you to repeatedly add the same key like the 3 identical (username) keys above.

The only things I've found are articles like Drop Foreign Key without knowing the name of the constraint? which only seem to show how you can get constraint names, not identify programmatically the constraints you want to drop.

Lastly if anyone knows why the hell mySQL would allow you to repeatedly add the same key (non-primary) to a table I'll be all ears. MySQL, you so crazy.

Perhaps, the `DISTINCT` keyword would help? Either way it is kind of weird thinking behind the logic of the db structure for me to come up with a straight answer. (as in why would one want to dynamically alter tables) — Xorifelse, Oct 13 '16 at 12:54
I did say don't ask why ;) But since you asked... one would want to dynamically alter a table if you (or someone in the past who left you a job) setup a database with the same table, distributed a replica out to 1000s of different places, but then deployed various patches to them. So we know some have constraints, some don't, and we can't be sure of the naming of the constraints. Don't you just love being a dev... — Dave P, Oct 13 '16 at 13:11

score 1 · Answer 1 · answered Oct 13 '16 at 13:20

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE table holds the information of which fields make up an index.

You can return the name of the index (or indexes) that relate to the given table with the given fields with the following query. The exists subquery makes sure that the index has both fields, and the not exists makes sure that the index does not have any other fields. Further restrictions, such as ordinal position can also be incorporated into the query.

select CONSTRAINT_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1
where TABLE_NAME='my_table'
    and CONSTRAINT_SCHEMA='myshema'
    and COLUMN_NAME='username'
    and exists (select 1
                from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
                where TABLE_NAME='my_table'
                    and CONSTRAINT_SCHEMA='myshema'
                    and COLUMN_NAME='role'
                    and t2.CONSTRAINT_NAME=t1.CONSTRAINT_NAME
                    and t2.CONSTRAINT_SCHEMA=t1.CONSTRAINT_SCHEMA)
    and not exists (select 1
                    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
                    where TABLE_NAME='my_table'
                        and CONSTRAINT_SCHEMA='myshema'
                        and COLUMN_NAME NOT IN ('username','role')
                        and t2.CONSTRAINT_NAME=t1.CONSTRAINT_NAME
                        and t2.CONSTRAINT_SCHEMA=t1.CONSTRAINT_SCHEMA)

However, the above query only returns the name of the index you want to drop (if any). You need to dynamically concatenate the sql command for dropping the index using a prepared statement.

Awesome thanks key_column_usage is a good find, and your sub queries nicely explained. One of the things this doesn't deal with is order of the columns in the index (i.e. it'd also find the `(role,username)` key if that existed) but you've given me a way forwards. Cheers. — Dave P, Oct 13 '16 at 14:48
As I wrote in the answer: "Further restrictions, such as ordinal position can also be incorporated into the query." If you click on the link to the documentation, you will find that ordinal_position column defines the ordinal position of the fields in the index. Just add it to your where clauses. — Shadow, Oct 13 '16 at 16:17

score 0 · Accepted Answer · edited Apr 07 '17 at 09:43

With the help of @shadow's tips on key_column_usage and the ordinal position, I've now created this nifty stored procedure that takes in a table, key definition (comma separated list of columns), and database. It then checks if a unique key(s) exist for that key definition and produces the SQL to drop them.

I've only produced SQL here to avoid people getting a destructive operation running this procedure, but simply remove the initial 'select' from @sqlstmt if you actually want it to do the business.

Could also modify this to take in the key type as a parameter rather than just rely on hardcoded UNIQUE.

CREATE PROCEDURE sp_drop_unique_key_if_exists
(
given_table    VARCHAR(64), 
given_key      TEXT, -- In comma sep form '(col1, col2, ...)' note the brackets are important!
db             VARCHAR(64)
)
BEGIN

DECLARE drop_constraints  TEXT;
DECLARE dbase             VARCHAR(64);

SET dbase = IFNULL(db, 'my_db');

SELECT      group_concat('DROP KEY `',unique_constraints, '`')
            INTO drop_constraints
  FROM            
    ( SELECT      IF( REPLACE(given_key, ' ', '') 
                       = CONCAT('(', GROUP_CONCAT(kcu.column_name ORDER BY ordinal_position), ')'),
                      tc.constraint_name, null)
                  AS unique_constraints
      FROM        INFORMATION_SCHEMA.table_constraints tc 
      INNER JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
                  ON kcu.constraint_name = tc.constraint_name
                  AND kcu.constraint_schema = tc.constraint_schema
      WHERE       tc.table_name = given_table AND tc.constraint_schema = dbase
                  AND tc.constraint_type='UNIQUE'
      GROUP BY    tc.constraint_name
    );

-- drop the key or keys
IF drop_constraints IS NOT NULL THEN
    SET @sqlstmt = CONCAT('SELECT \"ALTER TABLE ',dbase,'.', given_table, ' ',drop_constraints,'\"');
    PREPARE st FROM @sqlstmt;
    EXECUTE st;
    DEALLOCATE PREPARE st;
ELSE
    SELECT CONCAT('Cannot find key ', given_key, ' on ', given_table) DropUniqueKeyErrorMessage;   
END IF;

END $$

Mysql : Drop a unique key if exists -- but without knowing name of the key or whether it exists

2 Answers2