How to modify the value of a unique index

Question

CREATE TABLE `entityfeedhot` (
  `efhid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `idx` int(10) unsigned NOT NULL COMMENT 'order num',
  `age` int(10) unsigned NOT NULL COMMENT '',
  `gender` tinyint(4) unsigned NOT NULL COMMENT '，1：man，2：famal',
  `deleteflag` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`efhid`),
  UNIQUE KEY `idx_age_gender_deleteflag` (`idx`,`age`,`gender`,`deleteflag`),
  KEY `efid` (`efid`)
) ENGINE=InnoDB AUTO_INCREMENT=916 DEFAULT CHARSET=utf8 ;

now I want to make idx=idx-1 in eg. range(idx>1 AND idx<9 )

but due to UNIQUE KEY idx_age_gender_deleteflag I have an error

 Duplicate entry '4-0-1-0' for key 'idx_age_gender_deleteflag'

I want to know the best way to let idx=id-1 or idx=id+1

To possibly state the obvious: You want all the new values of your unique index to be `value - 1` ? — Martin, Apr 04 '19 at 07:58
idx is order number, change the order num for each year under one gender — Allen wong, Apr 04 '19 at 08:02

Martin · Accepted Answer · 2019-04-04T08:23:03.933

Three Methods - Method 1 is preferred:

1) Cycle through each entry and remove 1 from that entry value, starting at the lowest and working up.

UPDATE `entityfeedhot` SET `idx` = `idx` - 1 WHERE `idx` > 1 AND `idx` < 9 ORDER BY `idx` ASC

The ORDER BYclause is important as it ensures that rows are processed in ascending order, to avoid result duplicates. idx = 1 should not exist.

2) Make a new column such as idx_two and set this column value to equal idx - 1. This negates the index's unique status (because it's a new column).

ALTER TABLE `entityfeedhot` ADD `new_dx` INT(10) NOT NULL DEFAULT '0' AFTER `idx`;

Then populate the column:

UPDATE `entityfeedhot` SET `new_dx` = CASE
WHEN  `idx` > 1 AND `idx` < 9 THEN `idx` = `idx` - 1
ELSE `idx`

The above query will populate with idx - 1 where the WHEN condition is true, else it will just copy the value. All rows will be updated.

Then delete the idx column and rename the new column. Indxes will need to be rebuilt.

3) Turn off your unique status of your index.

Make your changes

Turn back on the unique status of your index.

Thank you very much, I feel so stupid, I will try every method. — Allen wong, Apr 04 '19 at 08:15
@Allenwong don't feel stupid, it's always easier once you know the answer. Happy to help! — Martin, Apr 04 '19 at 08:22

How to modify the value of a unique index

1 Answers1

Three Methods - Method 1 is preferred: