hretic hretic - 2 months ago 10
MySQL Question

adding unique to existing foreign key

i have this table

CREATE TABLE IF NOT EXISTS `transaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`amount` bigint(20) NOT NULL,
`req_id` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `transactions_873a2484` (`req_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=914 ;


i want to change this forign key
transactions_873a2484
to a unque forign key
basically i want to change it to

UNIQUE KEY `transactions_req_id_de2b5683_uniq` (`req_id`),


i already have lots of data in my table otherwise i would have just remake this table .... is there anyway to do this withouth harming the data ?

Answer

I will improve this as I go. MySQL will honor your wishes, even allow you to shoot yourself in the foot as you go:

create table t9
(
    id int auto_increment primary key,
    thing varchar(20) not null,
    key(thing),
    unique key (thing),
    unique key `yet_another` (thing)
);
-- warning 1831 dupe index
show create table t9;
CREATE TABLE `t9` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `thing` varchar(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `thing_2` (`thing`),
   UNIQUE KEY `yet_another` (`thing`),
   KEY `thing` (`thing`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So look at all the baggage it you have to carry around with your upserts (read: slow extra unnecessary indexes).

So if you want it as lean as possible, as I mentioned in comments, unwind things first by dropping the FK's in the child tables, the referencing first. See This Answer.

Then drop the current non-unique parent key.

DROP INDEX index_name ON tbl_name

Add the unique key in the parent. This is the referenced.

Then add the FK's in the children (the referencing)

You can get the key names by show create table theTableName or by SHOW INDEX.

Such as:

mysql> show index from t9;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t9    |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t9    |          0 | thing_2     |            1 | thing       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t9    |          0 | yet_another |            1 | thing       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t9    |          1 | thing       |            1 | thing       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Comments