mysql inserting unique row except the key

Question

| id |  -> primary key, auto_increment
| v1 |
| v2 |

I want to make sure that there are no duplicate (v1, v2) pairs on insert. Is it possible via an SQL statement? Currently I do a select first and if there 's no result I continue with the insert.

I was wondering if there 's a better solution.

score 2 · Accepted Answer · answered Oct 18 '12 at 19:27

2

If you don't mind an error coming back from the request (you can handle it in your code or as noted elsewhere do your inserts with INSERT IGNORE to totally suppress the error.), you could put a UNIQUE compound index on v1, v2.

ALTER TABLE tablename
      ADD UNIQUE INDEX unique_pair(v1,v2);

answered Oct 18 '12 at 19:27

Ray

40,256
21
101
138

Agreed, and some extra info http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql since I was in the process of answering – Mike Park Oct 18 '12 at 19:28
I wasn't aware of compound indexes :o Makes perfect sense and along with @GrailsGuy suggestion, it 's exactly what I was looking for! Thank you! 8 more mins limit to mark the answer :o – johnjohn Oct 18 '12 at 19:31
1

Also you have to specify `not null' when you create those fields – jcho360 Oct 18 '12 at 19:59

score 1 · Answer 2 · answered Oct 18 '12 at 19:28

1

I believe that the way you're doing it is actually the preferred method. However, you can try to use INSERT IGNORE, if you've got the proper constraints on the columns.

answered Oct 18 '12 at 19:28

Igor

33,276
14
79
112

mysql inserting unique row except the key

2 Answers2