In what situation must we define a referencing table in a relationship as a foreign key?

Question

In this Diagram

Diagram

the Enrolls table (at the top) only has Courses defined as a foreign key while in the Supplies table (at the bottom), every referenced Primary Key of the original tables is defined as foreign key.

Why is this so? Thank you!

Unrelated to your question, but: you should never use the `char()` data type. — , Jan 31 '22 at 09:34
Not sure what your question is. What foreign keys are you missing in the `enrolls` table? It does define `sid` as a foreign key to `students` — , Jan 31 '22 at 09:36
@a_horse_with_no_name, my confusion stems from the Enrolls table not explicitly defining `sid` as a foreign key while `cid` is. As opposed to the Supplies table explicitly writing "foreign key (xid)" for each of the foreign keys. Why is this done? Thanks! — FlyingSquid, Jan 31 '22 at 09:53
The `enrolls` table does define `sid` explicitly as a foreign key: `sid char(20) REFERENCES students` - what's not "explicit" about that? It's just a different syntax that does the same as the one used for `cid` — , Jan 31 '22 at 09:57
@a_horse_with_no_name, I see what you are saying. May I ask if it is possible for the Enrolls table to then omit the line `foreign key(cid) reference Courses` and instead put `references Courses` on line 3 where the `cid` is defined? Would this mean the same thing? Thanks! — FlyingSquid, Jan 31 '22 at 10:02
Yes, that would mean the same thing. See here: https://stackoverflow.com/a/28560619 — , Jan 31 '22 at 10:07

score 0 · Accepted Answer · answered Jan 31 '22 at 17:13

Both references in Enrolls are defined as foreign keys like they should. Only the foreign key on sid is written using the "column constraint" syntax, while the one on cid is written using the "table constraint" syntax.

The end effect is the same; it doesn't matter which syntax variant you use.

In what situation must we define a referencing table in a relationship as a foreign key?

1 Answers1