为现有外键添加唯一 [英] adding unique to existing foreign key

问题描述

我有这张桌子

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 ;

我想将此外键transactions_873a2484更改为唯一的外键 基本上我想将其更改为

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 ?

推荐答案

我将逐步改进这一点. MySQL将兑现您的愿望，甚至允许您随行随地射击:

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).

因此，如您在我的评论中所提到的那样，如果您希望它尽可能地精简，请先将FK放到子表中，然后放开 referring ，以展开内容.请参阅此答案.

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);

然后在子级中添加FK(引用)

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

CREATE INDEX idxName ON child_tbl_name (colName);

您可以通过show create table theTableName或 SHOW INDEX .为新名称使用新名称，没关系.

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.

例如:

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

这篇关于为现有外键添加唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！