Technology LearningAlmost every table in a real database has a primary key, and most tables also carry one or more foreign keys. The names rhyme, which is unfortunate, because their jobs are opposites. One identifies a row. The other refers to a row somewhere else. Once that split is clear, the way tables connect stops being mysterious.
Primary key: the row's name tag
A primary key is the column, or set of columns, that uniquely identifies each row in its table. No two rows share the same value, and it is never empty. It is how the database, and you, point at exactly one row and no other.
A patient table's primary key might be a patient identifier. Give me patient 4471 and there is precisely one row that means. That certainty is what everything else builds on.
Good primary keys are stable and meaningless. A value that never changes and carries no other information, often a plain auto-generated number, makes the safest key. Using something like an email address as the key seems convenient until someone changes their email and every reference to them breaks.
Foreign key: a pointer to another table's row
A foreign key is a column that holds the primary key value of a row in another table. It does not identify its own row. It says "this row belongs with that one over there".
A visit table has its own primary key for each visit, and it also has a patient column holding a patient identifier. That patient column is a foreign key. It links each visit to exactly one patient, without copying the patient's details into the visit.
So the two keys work as a pair across the gap between tables: the primary key names a patient once, and every foreign key that stores that value points back to the same named patient.
What the link actually buys you
Declaring a foreign key does more than document intent. The database will refuse to store a visit for a patient who does not exist, and it can refuse to delete a patient who still has visits, or clear the link when you do. This is called referential integrity, and it is the guardrail that stops your data drifting into orphaned, pointing-at-nothing rows.
A common point of confusion
A foreign key in one table points at a primary key in another, but the two need not have the same column name, and the foreign key column is not unique in its own table. Many visits can point at the same patient. That is the normal one-to-many shape: one patient, many visits, each visit carrying the one patient's key.
Where to go next
Keys are the connective tissue of a relational database, and getting them right is what lets a design grow without turning into a tangle. Keys, Links & Constraints covers primary keys, foreign keys and the rules that protect them, and Model to Tables: Relational Mapping shows where those keys come from when you turn a design into real tables.