MySQL: Unique constraint on multiple fields

Question

I have two tables --> Variables (id, name) and Variable_Entries (id, var_id, value).

I want each variable to have a unique set of entries. If I make the value entry unique then a different variable won't be able to have that same value which is not right.

Is there some way to make the value column unique for identical var_id's?

Answer 1

Add a unique key of Variable_Entries for the combined fields var_id/value.

Also, you should always use singular words for table names (user instead of users). Never use uppercase characters in the table name, because that will cause you a NIGHTMARE over different operating systems.

Answer 2

Yes:

alter table Variable_Entries add unique (var_id, value);

Now you have a unique constraint across var_id and value together. In other words, no occurrence of var_id and value can appear more than once.

Answer 3

Yes, you can create a composite unique key:

ALTER TABLE variable_entries ADD UNIQUE (var_id, value);