0

Im new to SQL, and am using SQL Server Managmetn Studio V11

Im having trouble with my structure:

for example if I have a Table Fruit:

FruitID, Name , FamilyID, ColourID

Table Family

FamilyID, Name

Table AllowedColour

ID, FamilyID, ColourID
1,     1,     56
2,     1,     88
3,     1,     99
4,     2,     56
5,     2,     57

So in Allowed Colour I list the Colours that a certain family can have. How do I restrict the values entered in the Fruit table so that only Allowed colours can be given to the fruit?

Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
MrB
  • 35
  • 7

1 Answers1

1

Add a unique constraint to AllowedColors. (And consider dropping the column "ID".)

alter table AllowedColors
add constraint your_constraint_name
unique (FamilyID, ColorID);

You probably want each of those columns to be declared NOT NULL, too. I'll leave that to you.

Now you can use that pair of columns as the target of a foreign key constraint.

alter table fruit
add constraint another_constraint_name
foreign key (FamilyID, ColorID) 
  references AllowedColors (FamilyID, ColorID);

You'll also want a foreign key from AllowedColors.FamilyID to Family.FamilyID.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thank you for your reply. This has helped me very much, I didnt realise I could use a Unqiue Constraint in that way – MrB Jul 12 '13 at 09:15
  • If you drop the column "ID", you can declare {FamilyID, ColorID} the primary key instead of declaring them unique. Everything else will work the same. – Mike Sherrill 'Cat Recall' Jul 12 '13 at 10:14
  • Is this method suitable to build a "dictionary" of valid values or value combinations in practice? – Aurus Huang Mar 27 '19 at 09:37
  • @AurusHuang: I'm not sure what you mean. In a SQL dbms, valid values are usually stored in a table; other table set a foreign key reference to that table. [See this dba.stackexchange answer.](https://dba.stackexchange.com/a/9065/1064) Is that what you're thinking about? – Mike Sherrill 'Cat Recall' Mar 27 '19 at 15:26
  • @MikeSherrill'CatRecall' That's exactly what I want! Thank you. – Aurus Huang Mar 28 '19 at 06:06
  • Foreign key requires either unique or primary key constraint. What if we want to limit to a non-key column with repetitive values? (Just to make sure data will be valid) – Saleh Jun 22 '21 at 05:37
  • @Saleh: To validate data, create a table of valid values, and set a foreign key reference to that table. For example, see [this answer](https://stackoverflow.com/a/17379201/562459), [this answer](https://stackoverflow.com/a/8996345/562459), and [this answer](https://stackoverflow.com/a/7604737/562459). – Mike Sherrill 'Cat Recall' Jun 22 '21 at 18:46