Rows in different tables often need to refer to the same real-world entity. Keys give those references a stable relational structure.

Candidate and primary keys

A candidate key is a set of columns whose values uniquely identify each row. A table may have several possible candidate keys.

One is commonly chosen as the primary key. For example,

users
id | name
1  | Ada
2  | Lin

can use id as the primary key.

Foreign keys

A foreign key stores a value that must correspond to a key in another relation.

posts
id | author_id | title
10 | 1         | Notes

Here author_id = 1 refers to the user whose primary key is 1.

Relationship shapes

A foreign key naturally represents many-to-one relationships: many posts can refer to one author.

A one-to-one relationship adds an appropriate uniqueness constraint. A many-to-many relationship is commonly represented by a junction table containing foreign keys to both sides.

Referential integrity

Foreign-key constraints prevent references to rows that do not exist and define what may happen when referenced data is updated or deleted.

Keys are therefore not merely convenient IDs for application code. They express identity and relationships as enforceable parts of the database model.