How to find the name of a unique index? MySQL

Question

I have something like this. I want to remove the unique index on columns: long_col_name and some_other_id. As both the table name and the columns names are quite long and all three have underscores in them, how do I remove this unique index?

mysql> SHOW INDEXES FROM long_table_name;
+--------------------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                    | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| long_table_name          |          0 | PRIMARY  |            1 | id             | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
| long_table_name          |          0 | unique   |            1 | long_col_name  | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
| long_table_name          |          0 | unique   |            2 | some_other_id  | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

I'm using Laravel but I just need something that will work in either Laravel or MySQL.

Is the unique key called this... long_table_name_long_col_name_some_other_id_unique?

How would I remove the index like this in mysql?

ALTER TABLE long_table_name DROP ???;

Or using Laravel..

        if (Schema::hasColumn('long_table_name', 'long_col_name')) {
            Schema::table('long_table_name', function (Blueprint $table) {
                $table->dropUnique('long_table_name_long_col_name_some_other_id_unique');
            });
        }

Gives the error...

SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'long_table_name_long_col_name_some_other_id_unique'; check that column/key exists (SQL: alter table `long_table_name` d  
rop index `long_table_name_long_col_name_some_other_id_unique`)

I get the same error when I do...

$table->dropUnique(['long_col_name','some_other_id']);

Any ideas?

This SO question might be helpful here. Your question seems not have to do with how to drop a unique constraint, but rather how to find the names of the constraints. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 31, 2018 at 11:53
Thank you. The actual name of the unique key was "unique", that's what's in the table in the OP so maybe I shouldn't be surprised. — sdexp
– sdexp, Commented Oct 31, 2018 at 12:25

DPS · Accepted Answer · 2018-10-31 12:14:17Z

1

First you need to check whether the indexes are present in the table, If so get the key name using Collection and convert it into array. Check that key is in array, if not drop otherwise you can add index to the respective column.

 $key_names = collect(DB::select("SHOW INDEXES FROM long_table_name"))->pluck('Key_name')->toArray();



if (in_array("long_table_name_long_col_name_some_other_id_unique", $key_names)) {
            Schema::table('long_table_name', function (Blueprint $table) {
                $table->dropIndex( 'long_table_name_long_col_name_some_other_id_unique');
            });
        }

answered Oct 31, 2018 at 12:14

DPS

1,00313 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sdexp Over a year ago

Thanks, I followed @Tim-Biegeleisen's link which confirmed the key name was actually "unique". Once I had that, it was just $table->dropIndex( 'unique');

Collectives™ on Stack Overflow

How to find the name of a unique index? MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related