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?

score 12 · Accepted Answer · answered Mar 23 '10 at 22:22

12

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.

answered Mar 23 '10 at 22:22

Ross Snyder

1,945
11
11

thanks, and people below too-- I couldn't find this anywhere – Cypher Mar 23 '10 at 22:25
Is there a way to enforce this when creating the table? – puk Apr 22 '12 at 00:16
2

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) )` – Kelly Larsen Dec 17 '12 at 05:10
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? – Scorb Feb 28 '20 at 01:39
@Scorb yes. That's what it means. – veritas Jun 25 '21 at 10:36

score 6 · Answer 2 · answered Mar 23 '10 at 22:23

6

Yes, you can create a composite unique key:

ALTER TABLE variable_entries ADD UNIQUE (var_id, value);

answered Mar 23 '10 at 22:23

Daniel Vassallo

337,827
72
505
443

Thanks for the word *composite*, made me understand what it does. – Prof. Falken Feb 07 '13 at 14:21

score 0 · Answer 3 · answered Mar 23 '10 at 22:21

0

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.

answered Mar 23 '10 at 22:21

Amy B

17,874
12
64
83

I wonder why should we always use singular names for table names? – Daniel Vassallo Mar 23 '10 at 22:24
"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). – Ross Snyder Mar 23 '10 at 22:24
CakePHP depends on plurals for table names... It's a funny comment :) Uppercase, though... I agree – luigi7up Jun 22 '11 at 08:16
Plural vs singular table names is a religious debate http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names – Steve Kuo Oct 18 '12 at 08:13

MySQL: Unique constraint on multiple fields

3 Answers3

Linked