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?

Solution 1

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.

Solution 2

Yes, you can create a composite unique key:

ALTER TABLE variable_entries ADD UNIQUE (var_id, value);

Comments

[Daniel Vassallo]

I wonder why should we always use singular names for table names?

[Ross Snyder]

"You should always use singular words for table names" - I disagree. I always use the plural, and I know I'm not alone (ActiveRecord, for example, does the same).

[Cypher]

thanks, and people below too-- I couldn't find this anywhere

[luigi7up]

CakePHP depends on plurals for table names... It's a funny comment :) Uppercase, though... I agree

[puk]

Is there a way to enforce this when creating the table?

[Steve Kuo]

Plural vs singular table names is a religious debate stackoverflow.com/questions/338156/…

[Kelly Larsen]

you may not be wanting to know but for the sake of others, create table tbl_table ( id integer not null auto_increment, fname varchar(255), lname varchar(255), CONSTRAINT tbl_table PRIMARY KEY (id), unique (fname,lname) )

[Prof. Falken]

Thanks for the word composite, made me understand what it does.

[Scorb]

Does that mean that a particular value of var_id can show up more than once, but a particular combination of var_id and value can not show up more than once?

[veritas]

@Scorb yes. That's what it means.