Re: insert in an ordered set, then reorder

[Barry Galbraith]

Is this what you mean?

CREATE TABLE colors (
  id int(11) NOT NULL AUTO_INCREMENT,
  color varchar(50) NOT NULL,
  weight int(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY IDX_weight (weight)
) ;

INSERT INTO colors (color, weight) VALUES ('blue', 2);
INSERT INTO colors (color, weight) VALUES ('red', 3);
INSERT INTO colors (color, weight) VALUES ('black', 1);
INSERT INTO colors (color, weight) VALUES ('white', 4); 

select id, color, weight from colors order by weight;
+----+-------+--------+
| id | color | weight |
+----+-------+--------+
|  8 | black |      1 |
|  6 | blue  |      2 |
|  7 | red   |      3 |
|  9 | white |      4 |
+----+-------+--------+
4 rows in set (0.00 sec)

UPDATE colors SET weight = weight + 1 WHERE weight > 1 ORDER BY weight DESC;

INSERT INTO colors (color, weight) VALUES ('orange', 2);

select id, color, weight from colors order by weight desc;
+----+--------+--------+
| id | color  | weight |
+----+--------+--------+
|  9 | white  |      5 |
|  7 | red    |      4 |
|  6 | blue   |      3 |
| 10 | orange |      2 |
|  8 | black  |      1 |
+----+--------+--------+
5 rows in set (0.00 sec)

Bottom line is there is no way for MySQL to alter the unique key 'weight' for you. You need to do that yourself.

Good luck,
Barry.