mercredi 31 octobre 2018

How to find the name of a unique index? MySQL

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?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire