mysql combined unique keys

mysql unique-constraint
David picture David · Aug 25, 2012 · Viewed 24.5k times · Source

Is there way in MySQL to have two unique keys and connect them somehow?

for example if i have the following table and 'title'and 'store' are a unique keys

id | category | title       | price | store
1  | outdoors | fishing rod | 59.99 | wal-mart
2  | auto     | Penzoil Oil | 9.99  | target

and i try to insert the following record. This new record would be ignored because the title is "fishing rod" AND the store is 'wal-mart' and there is an existing record with that title and store

   | outdoors | fishing rod | 30.99 | wal-mart

but if i attempted to insert the following record it would be accepted because there isn't a record that exists with the title of "fishing rod" and store of "target"

   | outdoors | fishing rod | 30.99 | target

is this possible with just MySQL?

Answer

Bugs picture Bugs · Aug 25, 2012

You can define an index on multiple columns, e.g.:

CREATE UNIQUE INDEX arbitrary_index_name ON table_name (title, store);

Related questions

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

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. …

Read More
MySQL - Make an existing Field Unique

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this …

Read More
MySQL delete multiple rows in one query conditions unique to each row

So I know in MySQL it's possible to insert multiple rows in one query like so: INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6) I would like to delete multiple rows in a similar way. I know it's possible to delete multiple …

Read More