10

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?

Improve this question
0

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

thanks, and people below too-- I couldn't find this anywhere
Is there a way to enforce this when creating the table?
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) )
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 yes. That's what it means.
6

Yes, you can create a composite unique key:

ALTER TABLE variable_entries ADD UNIQUE (var_id, value);
Improve this answer

1 Comment

Thanks for the word composite, made me understand what it does.
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.

Improve this answer

4 Comments

I wonder why should we always use singular names for table names?
"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).
CakePHP depends on plurals for table names... It's a funny comment :) Uppercase, though... I agree
Plural vs singular table names is a religious debate stackoverflow.com/questions/338156/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.