How to add a two-field constraint on a table?

Asked Jun 01 '14 at 00:20

Active Jun 01 '14 at 00:20

Viewed 18 times

I have a stock table, with products:

STOCK_ID | SUPPLIER_ID | ITEM_ID
1        | 1           | 1
2        | 1           | 2
3        | 1           | 3
4        | 2           | 1
5        | 2           | 2
6        | 2           | 3

I want a constraint that will stop anyone from being able to add an item_id that already exists for a supplier. So in other words, in the example above, I must be able to add item_id 4 for supplier 1 and 2. But I must not be able to add another entry of items 1- 3 for either supplier 1 or 2.

I'm not sure what this is called or how you create such a limitation. But I know you can stop the user at a DB level from entering data like this. I basically want to put a block in place from adding data to the DB that an entry technically already exists for.

How do I do this in MySQL?

asked Jun 01 '14 at 00:20

rockstardev

13,479
39
164
296

it is called unique index – Iłya Bursov Jun 01 '14 at 00:22
3

Sounds like you want a UNIQUE constraint on the (supplier_id, item_id) combination. http://stackoverflow.com/questions/6679122/mysql-unique-constraint – Chris Hayes Jun 01 '14 at 00:22
i want unique, but didnt know its as simple as combining it over two fields. Thank you! – rockstardev Jun 01 '14 at 00:24
@coderama when you have multiple columns involved it is known as a composite or compound key BTW. – Martin Smith Jun 01 '14 at 00:25

How to add a two-field constraint on a table?

0 Answers0