Take the 2-minute tour ×
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.

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?

share|improve this question
1  
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
add comment

1 Answer

up vote 6 down vote accepted

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

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

Your Answer

 
discard

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.