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.

Gordon Linoff · Accepted Answer · 2015-10-15 02:24:28Z

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.

Community · Accepted Answer · 2017-05-23 12:29:55Z

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

2 Answers 2