Results 1 to 10 of 10

Thread: Foreign keys

  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Foreign keys

    Hello,

    I have a database with these tables:

    COMPETITIONS
    IdCompetition (PK)
    Name
    ...

    TEAMS
    IdTeam (PK)
    Name
    ...

    TEAMS_COMPETITIONS
    IdTeam (PK)
    IdCompetition (PK)
    (FK1 - IdTeam to table TEAMS)
    (FK2 - IdCompetition to table COMPETITIONS)

    TROPHIES
    IdTrophy (PK)
    IdCompetition
    IdTeam

    The question is what would be the foreign key on the TROPHIES table?
    a)
    Have 2 foreign keys, one IdCompetition to the table COMPETITIONS
    The other one IdTeam to the table TEAMS

    b) Have only one foreign key:
    IdCompetition, IdTeam to the table TEAMS_COMPETITION

    Any idea which one is the correct???

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by Juanster View Post
    The question is what would be the foreign key on the TROPHIES table?
    My assumption is that a team cannot win a trophy when it did not participate in the corresponding competition.
    So I'd say a single FK needs to go to TEAMS_COMPETITIONS

    Can you have more than one trophy per competition? If no, then the artificial PK on TROPHIES is not needed as well. But then the question comes up, what else do you want to store in the TROPHIES table that couldn't be stored the TEAMS_COMPETITIONS table (and maybe the competitions table)

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    A Team can't win a trophy unless is playing in a competition, you are right.

    I create a Trophies table because one team can win several trophies in the same competition and also there could be several trophies per competiton.

    Thanks

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by Juanster View Post
    I create a Trophies table because one team can win several trophies in the same competition and also there could be several trophies per competiton.

    Thanks
    Ok, then the separate PK for the trophies table does make sense.

  5. #5
    Join Date
    Jul 2010
    Posts
    3
    You mean the one I have just one FK?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by Juanster View Post
    You mean the one I have just one FK?
    Yes. Keep IdTrophy and make (IdCompetition, IdTeam) a FK to TEAMS_COMPETITIONS

  7. #7
    Join Date
    Jul 2010
    Posts
    8
    Foreign key(FK) is completely based on the data you want to "Retrieve"......

    When it comes to the case of "Trophies" then obviously you would want to know which team has won the Trophy....

    So "Teams" idTeam(PK) would be the foreign key in "TROPHIES" table idTeam(FK)....

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ram2Curious View Post
    Foreign key(FK) is completely based on the data you want to "Retrieve"......
    i think i know what you wanted to convey with this statement, but it did not succeed

    more correct would be to say that the FK is completely based on the data you want to integrificate

    in other words, FKs are about Referential integrity

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2010
    Posts
    8
    Integrificate ???????

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ram2Curious View Post
    Integrificate ???????
    yes, integrificate (verb, transitive): to cause [a schema] to have relational integrity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •