ON DUPLICATE KEY: multi-column unique constraint

Question

Right now I have:

INSERT INTO mytable (a,b,c,d) VALUES(1,2,3,4)
  ON DUPLICATE KEY UPDATE c=VALUES(c),d=VALUES(d)

which works if a or b are UNIQUE keys...

But now I want to UPDATE only when another row with the pair (a,b) doesn't exist in the table (otherwise skip insertion).

Basically (a,b) shoud be UNIQUE, not (a) or (b), but both connected.

For example these rows would be valid

ID (auto-inc) | a | b |  c  | d
            0 | 5 | 1 | 343 |466
            1 | 5 | 2 | 363 |466
            2 | 5 | 3 | 343 |496
            3 | 7 | 1 | 343 |496

Because there's 5,1, 5,2, 5.3, 7.1 etc.

But row #2 here should be considered duplicate of row #1, so row #1 should be updated:

ID (auto-inc) | a | b |  c  | d
            0 | 5 | 1 | 343 |466
            1 | 5 | 1 | 363 |466
            2 | 5 | 3 | 343 |496
            3 | 7 | 1 | 343 |496

Is this possible?

score 14 · Accepted Answer · answered Jul 31 '11 at 12:54

14

make UNIQUE KEY to (a,b) not to b

ALTER TABLE tblname ADD UNIQUE (a,b)

answered Jul 31 '11 at 12:54

RiaD

46,822
11
79
123

score 1 · Answer 2 · answered Oct 18 '16 at 22:13

1

It's worth noting that if you try to add the ADD UNIQUE(a,b) while there is data in the table, you'll likely get a duplicate key error. Empty the table, add the unique index, and it'll work.

answered Oct 18 '16 at 22:13

CrookedCreek

31
2

score 1 · Answer 3 · answered Jul 31 '11 at 12:55

1

CREATE UNIQUE INDEX index_unique_on_a_and_b ON mytable (a,b)

answered Jul 31 '11 at 12:55

M. Cypher

6,966
2
34
34

ON DUPLICATE KEY: multi-column unique constraint

3 Answers3

Linked