adding unique to existing foreign key

Question

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 ?

is the data unique? How many referencing tables do you have to this *referenced* — Drew, Sep 06 '16 at 19:46
You could do it from the child-up (child first). http://stackoverflow.com/a/838412 — Drew, Sep 06 '16 at 19:47
@Drew yes data is unique , 1 table ... basically i can make req_id unique easily withouth losing data with `ALTER TABLE transactions ADD UNIQUE(req_id);` but i dont want to add new key to table — hretic, Sep 06 '16 at 19:53

score 1 · Accepted Answer · edited May 23 '17 at 10:29

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;

Then add the unique key in the parent. This is the new referenced:

CREATE UNIQUE INDEX idxName ON tbl_name (colName);

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

CREATE INDEX idxName ON child_tbl_name (colName);

You can get the key names by show create table theTableName or by SHOW INDEX. Use fresh names for the new ones, it doesn't matter.

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      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

score 0 · Answer 2 · answered Sep 06 '16 at 20:47

0

ALTER TABLE `transaction`
    DROP INDEX `transactions_873a2484`,
    ADD UNIQUE(req_id);

You cannot turn a non-unique into UNIQUE, but the above should do the equivalent. The data will be unharmed.

answered Sep 06 '16 at 20:47

Rick James

135,179
13
127
222

adding unique to existing foreign key

2 Answers2

Linked