The purpose of a primary key is to uniquely identify the records in a table. The purpose of a foreign key is to link a (child) record to the (parent) record that "owns" it.
For example, EID might be used as the primary key of the UT student table. Each student must have a different EID. There may be multiple students named John Smith. There may be multiple students living at 1234 Main Street. There may be multiple students born on 1/1/1989. There may even be multiple students named John Smith living at 1234 Main and born on 1/1/1989. But, there can only be one UT student with an EID of Texas#1.
There are 50,000 records in the UT student table. If, on average, each of these students has completed 20 courses, then there will be 1,000,000 records in the UT grade table. Foreign keys allow us to keep the million As, Bs, Cs and Ds straight. Every grade record should include the EID of the actual student who earned the grade. The EID in the grade table is a foreign key that points to the unique parent/student who owns the grade.
| EID (pk) | First | Last | Etc |
|---|---|---|---|
| Texas#1 | John | Smith | blah blah blah |
| AW-1234 | Alice | Washington | |
| AdamsML | Mary Lynn | Adams | |
| BubbaJohn | John | Smith | |
< 49,995 missing records > | |||
| RCubed | Richard Ryan | Rollins | |
| GradeID | Course | Grade | EID (fk) | Etc |
|---|---|---|---|---|
| 1 | ACC 312 | A | Texas#1 | yada yada |
| 2 | M 340L | B | RCubed | |
| 3 | LEB 323 | A | Texas#1 | |
| 4 | CS 301 | B | AW-1234 | |
| 5 | ACC 312 | D | BubbaJohn | |
| 6 | FIN 357 | C | RCubed | |
| 7 | CS 301 | B | Texas#1 | |
| 8 | MKT 337 | D | BubbaJohn | |
| 9 | H 314 | A | AW-1234 | |
< 999,990 missing records > | ||||
| 1,000,000 | MIS 333K | A | AdamsML | |
The foreign key EID field in the grade table shows that three of the million UT grades are owned by John Smith (Texas#1). He received As in Accounting and Business Law and a B in Computer Science. Fortunately for him, the foreign key helps keep his good grades separate from the poor grades received by the "other" John Smith (BubbaJohn).