Stockertim Dictionary · Technology
Foreign key
Also called foreign keys, FK, referencing key
In short
A column in one table whose values refer to the primary key of another table, linking each row to the row it describes, such as an order to its customer.
- Beginner
- Concept
- Checked
In detail
A foreign key is the link that makes a relational database relational. It is a column, or a set of columns, in a child table that holds values copied from the primary key of a parent table. A loans table might have a column member_id whose every value must match a member_id in the members table; the loan row then belongs to exactly one member.
When the key is declared as a constraint, the database refuses changes that would break the link. Inserting a loan for member 9999 fails if no such member exists, and deleting a member who still has loans is either blocked or, if the designer chose a cascading rule, removes or updates the loans too. This property is called referential integrity: no row points at nothing.
Foreign keys usually carry many-to-one relationships. Many loans point to one member, many invoice lines point to one invoice, many sales point to one product. Knowing which side is the many side matters when writing joins, because joining from the one side to the many side repeats the parent row once for each child.
Spreadsheets have no such constraint, so a code typed with a trailing space, a lower-case letter or a leading zero dropped by the program simply fails to match. In imported data the same problem appears as orphan rows: children whose parent is missing. Counting rows that have no match is one of the first checks on any new data set.
Worked example
A lending library in Nairobi keeps members and loans in two tables. The loans table has 2,000 rows and its member_id column is a foreign key to members.
- A check finds 25 loan rows whose
member_idhas no matching member, left behind when old member records were deleted without a declared constraint. - Share of orphan loans: 25 / 2,000 = 1.25%
The library restores the missing members from a backup and declares the foreign key, so the database blocks the same mistake in future.
In practice
Database designers declare foreign keys when they create tables; analysts read them from a schema diagram or a data dictionary to learn how tables connect. In a spreadsheet the same role is played by a code column used with XLOOKUP or VLOOKUP to fetch details from another sheet.
Watch out
- A foreign key value need not be unique: one parent can have many children, and joins repeat the parent row for each.
- Codes that look the same can differ in type or spacing, so 00123 stored as text does not match 123 stored as a number.
Learn it properly
Official sources
- The Government Data Quality Framework · UK Government Data Quality Hub
- API technical and data standards · Government Digital Service
Facts last checked on 9 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.