Can a foreign key reference multiple tables? [duplicate]

Question

So I'm doing a bit of practise in database design, and a question arose that I couldn't figure out how to correct.

I'm pretty new at this, so I'm not the best but here it goes.

So I want to create two different tables, one called Team, and one called Player. These tables are pretty different from one another.

I then want another table called Challenge, which I want to reference either Team or Player.

Basically there are competitions, and they are either competed individually, or as a team. I want a foreign key to reference Team, if its a team challenge, or Player if its individual.

I can't combine the two tables, as they contain very different elements.

Im just a bit confused as to how to do it. Can I have just one foreign key, that will reference one table or another. Or shall I have two, with one null.

Or can I add another ID key in the Team and Player. And then in Challenge if a new Type key indicated its a group, it'll reference Team, and if its individual, it references Player.

Again, pretty new at this, so hope I made sense.

Answer 1

You could add two columns in challenge: PlayerID & TeamID, which both can be NULL.

PlayerID references Player.id
TeamID references Team.id

example, a challenge has a player:

  PlayerID  = 14
  TeamID = NULL

Note that the referenced columns (Player.id & Team.id) must be defined as NOT NULL, since they are referenced from table challenge