SQL Server - Foreign Key Referencing Primary Key A OR B

Question

Say I have a table called Matchup that contains two sports teams. I also have a table called Pick that has a column that must match either Team_A OR Team_B. So it's a foreign key of one OR the other columns in Matchup. Is this possible?

Matchup
Team_A
Team_B

Pick
Pick_Team - FK Matchup (Must match Team_A or Team_B from Matchup).

Add your comment (astanders answer) to the question body. Now its clear you should have a third table with all teams, right? And table Pick can then have a FK that targets that table's PK. — OzrenTkalcecKrznaric, Aug 13 '13 at 05:19

score 0 · Answer 1 · answered Aug 13 '13 at 05:00

I dont think that this is the correct approach.

I would rather recomend that you add an additional field to table Matchup (lets say Pick) and add a CHECK CONSTRAINT to ensure that it is either Team_A or Team_B.

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators.

From FOREIGN KEY Constraints

In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

And it does not seem to be what you are looking for.

This may be what i'm looking for. To expand on the example, I have multiple users picking who is going to win that game, which is why I have it in a separate table. Putting Picks in the same table as Matchup would defeat this. Can a check constraint reference columns from a different table? — OperationNewDay, Aug 13 '13 at 05:11
From http://stackoverflow.com/questions/3880698/can-a-check-constraint-relate-to-another-table it seems like you can create a UDF which will accomplish what you need. — Adriaan Stander, Aug 13 '13 at 05:16

score 0 · Accepted Answer · answered Aug 13 '13 at 06:06

I would split your Matchup table into two: Matchup proper and MatchupDetails.

The Matchup table would have a MatchupID column as its primary key.

The MatchupDetails one would consist of at least two columns: MatchupID to reference the Matchup table and TeamID to reference the Team table (you do have one, don't you?). The two columns would form the table's composite primary key.

Finally, there would be this Pick table. Since you've got multiple users (as per one of your comments), there would need to be a UserID reference. Two more columns, MatchupID & TeamID would serve as a composite foreign key referencing the corresponding column set in MatchupDetails. And to ensure that one user can pick no more than one team from a match-up, a composite primary key of (UserID, MatchupID) should do.

To summarise, here's a complete outline of the relevant part of the schema:

Matchup:
```
MatchupID
PRIMARY KEY (MatchupID)
```

MatchupDetails:

MatchupID
TeamID
FOREIGN KEY (MatchupID)
FOREIGN KEY (TeamID)
PRIMARY KEY (MatchupID, TeamID)

Pick:

UserID
MatchupID
TeamID
FOREIGN KEY (UserID)
FOREIGN KEY (MatchupID, TeamID)
PRIMARY KEY (UserID, MatchupID)

Yes, I do have Team and User tables. I believe that this, or some close variation, is what I am looking for. Thanks! (I'd up-vote the response but I don't have enough reputation yet...) — OperationNewDay, Aug 13 '13 at 15:24
Ok I tried this out and it works great. I was confused at first because the Matchup table in the example above only has an ID. I added 'Matchup_Date' and 'Matchup_Venue' along with a few other columns and then it made perfect sense. — OperationNewDay, Aug 18 '13 at 06:54

SQL Server - Foreign Key Referencing Primary Key A OR B

2 Answers2