0

I'm wondering if it's possible to use one foreign key to connect to multiple tables?

I want to have athletes that can be either in a running or jumping event. For this they get an event number for the event they are in (they can only participate in one event).

For instance:


CREATE TABLE running (event_num decimal primary key,
event_time varchar(20) not null,
);

CREATE TABLE jumping (event_num decimal primary key,
event_time varchar(20) not null,
);

CREATE TABLE athletes (athlete_num decimal primary key,
event_num decimal not null,
);

insert into running VALUES
(111, "43 seconds");

insert into jumping VALUES
(222, "5 seconds");

insert into athletes VALUES
(101, 111),
(102, 222);

I want to link the athletes table to the jumping and running tables via one key (event_num).

The first row in "athletes" is linked to "running" via 111 (using the event_num foreign key).

The second row in "athletes" is linked to "jumping" via 222 (also using the event_num foreign key, but this time the linked value is found in the "jumping" table)

#What I'm trying to achieve
foreign key (event_num) REFERENCES running(event_num) AND REFERENCES jumping(event_num)

The first row in "athletes" is linked to "running" via 111 (using the event_num foreign key).

The second row in "athletes" is linked to "jumping" via 222 (also using the event_num foreign key, but this time the linked value is found in the "jumping" table)

Crocs123
  • 105
  • 7
  • How are you stuck? [mre] PS This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 15 '20 at 12:41
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 15 '20 at 12:47
  • Giving "code" that does not mean anything is not a helpful way to communicate. There are lots of easily found questions about wanting a value to appear in one of multiplle tables. But that's not a FK constraint. Also it is an antipattern for DB/SQL subtyping/inheritance. Also zillions of easily found posts. Another antipattern is multiple fks to multiple tables, radio button FKs. Also easily found. But you need to say what you mean clearly to research or communicate. – philipxy Oct 15 '20 at 12:48
  • No, a foreign key cannot reference multiple tables; a column can, if you define two foreign keys. The "column" is not the "foreign key". – The Impaler Oct 15 '20 at 13:07
  • Adding to that comment: Multiple FK constraints from the same columns (FK) can reference multiple targets. But, per what a FK constraint says, the subrow values in the source would have to be in all the targets. – philipxy Oct 15 '20 at 13:14
  • I spent time researching but couldn't find an answer. I hope the code and what I'm trying to achieve is clearer now. I just want to know if a single FK can be associated with multiple tables. Ideally I didn't want to have different FK's relating to the "jumping" and "athletes" tables. – Crocs123 Oct 15 '20 at 13:33
  • "a single FK can be associated with multiple tables" is not clear. What is "associated"? What is the limitation on DB states that you want? Don't use "FK" to try to say it when you're not sure what a FK or FK constriant is. What is "something like"? Do you mean, the example is a case of what you want? Use enough words. Say what you want in a clear generic statement. Please clarify via edits, not comments. Good luck. – philipxy Oct 15 '20 at 13:43

0 Answers0