Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Simple question probably.

For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does that work.


A                          B
TeamID                     EventID
teamName                   datetime
teamocation                homeTeam
etc                        awayTeam

So how do I define that homeTeam and awayTeam both utilise the Fk_Team relationship and that the homeTeam & awayTeam entry must exist in the TeamID.teamName field?

share|improve this question
Why are they referencing the TeamName instead of TeamID? – Aaron Bertrand Apr 25 '12 at 15:03
@Aaron Bertrand. True they are referencing TeamID for the details of the team name. – sayth Apr 25 '12 at 21:50
Ok so just keep that in mind for the code I posted below; obviously replace dbo.A(teamName) with dbo.A(TeamID)... – Aaron Bertrand Apr 25 '12 at 22:07
up vote 7 down vote accepted

You can't implement this as a single constraint; you'll need to create two:

  FOREIGN KEY (homeTeam) REFERENCES dbo.A(teamName);

  FOREIGN KEY (awayTeam) REFERENCES dbo.A(teamName);

As I alluded to in my comment, it would be much more efficient to store TeamID in the two columns in dbo.B - this way you are repeating two INTs in every row, instead of bulky strings like "Billy Bob's Bait and Tackle Mudhens" or "South Cincinnati Rebel Rockers."

It also makes it much easier to change a team's name (unless the point is to record their historical name at the time the game was played, but even then I would store names over time in some kind of history table, not with every game).

share|improve this answer
thanks for the help – sayth Apr 26 '12 at 3:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.