Multiple references

Martin Mocko 9 years ago updated by Rafał Strzaliński (Senior Engineer) 9 years ago 3
Hello, I would like to know how to create multiple foreign key references to only one primary key value in a different table. What I'm trying to accomplish is something like this - I have two tables - Team and Match. A match is always played between two teams, so I would like to have two foreign keys that reference a Team primary key (an id). However I seem to have a problem doing this because I'm getting an error which you can see in the image included. How can I avoid this error or what should I be doing differently ? The error arises as soon as I add the Team2_id foreign key. Thanks very much for the help.
Image 53
I'm sorry I already found a workaround -- I needed to create two references and give them a different name so that they don't have the same name (as they generically do when creating a reference) and then it works. However this solution seems a bit impractical - I may also need to have a Winner FK and Loser FK (for which it isn't really necessary to say they are foreign keys for Teams) - but if I wanted to make them FK, I would need to add 2 more references to do this.
Under review

Your discovery is not a workaround it's a the proper solution. References are not just connections between tables. References "reference" (point) rows in connected tables. Match is played by two teams (host,guest), so you two references.