Using a foreign key on multiple fields in one table

Question

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.

so

A                          B
TeamID                     EventID
teamName                   datetime
teamocation                homeTeam
etc                        awayTeam
                           Weather
                           Fk_team 

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?

Answer 1

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

ALTER TABLE dbo.B ADD CONSTRAINT FK_HomeTeam 
  FOREIGN KEY (homeTeam) REFERENCES dbo.A(teamName);

ALTER TABLE dbo.B ADD CONSTRAINT FK_AwayTeam 
  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).