Insert 2 values into mysql table only if both values are unique together

Question

I want data to be inserted into mysql table only if both fields are unique. For example:

ID   VALUE
__   _____
1    abc  //INSERT
2    abc  //INSERT
3    def  //INSERT
1    def  //INSERT
2    abc  //INSERT SHOULD NOT BE PROCESSED

WHats the most efficient way to do accomplish this and also only using mysql.

Answer 1

You need to create a unique index or constraint:

create unique index unq_t_id_value on t(id, value);

This will prevent duplicated values from being inserted into the table.

Answer 2

You can do a composite key (multi-column).

Create table myTable(
...
PRIMARY KEY (ID, VALUE)
)

When you try insert to

2    abc 

you will have duplicate entry error message and if you can not create the table you can ALTER TABLE like this