3

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?

3
  • 1
    Why are they referencing the TeamName instead of TeamID? Apr 25, 2012 at 15:03
  • @Aaron Bertrand. True they are referencing TeamID for the details of the team name.
    – sayth
    Apr 25, 2012 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)... Apr 25, 2012 at 22:07

1 Answer 1

7

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).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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