mySQL how to prevent insert unless combination of two columns (fields) are unique?

Question

Possible Duplicate:
How do I specify unique constraint for multiple columns in MySQL?

I am new to MySQL and have a table that I am converting to MySQL which contains "order guides" for accounts which are all the items an account normally orders. I need to prevent an insert in the case of an item (product number) for an account is already in the order guide table which is dependent on the combination of the uniqueness of two fields. Simplified example of the data in the order guide table:

uID  Account_Num   Prod_Num
0    1000          2000
1    1000          2010
2    1000          2020
3    1000          2030
4    1001          2000
5    1001          2010
6    1001          2020
7    1001          2021

I can not just make the "Account_Num" and "Prod_Num" fields unique. I need a way to set a rule or something to specify if the combination of "Account_Num" and "Prod_Num" are unique allow insert. The uID field is a unique auto increment field. Is this possible?

score 11 · Accepted Answer · answered Nov 12 '12 at 19:14

11

You'll be delighted to know that indexes can be multi-column :-)

ALTER TABLE my_table ADD UNIQUE account_prod (Account_Num, Prod_Num);

answered Nov 12 '12 at 19:14

Chris Trahey

17,799
1
37
51

AWESOME!! Thanks! I did not realize that the unique index acts as a rule. – LukeS Nov 12 '12 at 19:37

score 1 · Answer 2 · answered Nov 12 '12 at 19:14

1

create unique index mytable_idx1 on mytable(Account_Num, Prod_Num);

answered Nov 12 '12 at 19:14

Bohemian

365,064
84
522
658

score 0 · Answer 3 · answered Nov 12 '12 at 19:15

0

Sure.

Have a look at

INSERT INTO <yourtable> (cols)
  SELECT cols FROM <yourtable2>, <yourtable> where <yourcondition>

Make sure that the select only returns new entries.

answered Nov 12 '12 at 19:15

Udo Held

11,486
11
63
86

mySQL how to prevent insert unless combination of two columns (fields) are unique?

3 Answers3