Primary Keys and Foreign Keys

A primary key or foreign key identifies how rows are connected.

Programming

From Hidden Structure to Visible Relationships

A database can contain dozens or even hundreds of tables. When the relationships are kept only in your head, it becomes difficult to see which tables are connected, how many rows can be related, and whether a relationship is required. A data model diagram turns that abstract structure into something visible. Crow's Foot Diagrams show tables as boxes and relationships as lines with symbols at their ends, so a reader can reason about the design at a glance.

What does each visible part of a database relationship diagram represent?

Following an Identifier Across Tables

A primary key is a unique integer identifier that identifies one row within a table. The conventional name for a primary key is id. A foreign key is a column in another table that stores a primary key value from the referenced table. The foreign key therefore acts as a link: read its integer value, find the row whose primary key has that value, and use that row's other columns.

Worked example

Tracing a track to its artist

The Track table contains a foreign key value of 7 in its artist_id column. The Artist table contains a row whose id value is 7. How does the database identify the artist for that track?

1
Start in Track
Read the value stored in the Track row's artist_id foreign key column: 7.
2
Move to Artist
Use 7 as the value to search for in the Artist table's id primary key column.
3
Identify the row
The Artist row with id equal to 7 is the referenced row.
4
Use the related data
The other columns in that Artist row provide the artist information associated with the Track row.

Result

The Track row points to the Artist row whose primary key id is 7. The foreign key stores the reference; the primary key identifies the destination row.

How does a foreign key value in one table point to a specific row identified by a primary key in another table?

In the source example, the Artist table contains artist information and the Track table contains song information. The Track table's foreign key references the Artist table's primary key. This lets the database represent which artist created which track without repeating all of the artist's information in every Track row.

Primary Keys and Foreign Key Names

Definition

Primary key: a unique integer identifier that uniquely identifies each row within a table. It is conventionally named id.

Definition

Foreign key: a column in one table that stores a primary key value from another table, creating a link between the two tables.

Column role	Typical naming pattern	What the value does
Primary key	id	Uniquely identifies a row within its own table
Foreign key	table_name_id	Stores a primary key value from another table

Naming conventions make the direction and purpose of a key easier to recognize.

How does a primary key distinguish one row from every other row in the same table?

Reading Crow's Foot Symbols

A Crow's Foot Diagram places symbols at both ends of a relationship line. Read each end from the perspective of the table where that end is attached. Cardinality describes how many related records are possible. A single dash represents one, while a crow's foot represents many. Optionality describes whether the relationship is required. A perpendicular dash represents mandatory, while a circle represents optional.

Symbols at one end	Cardinality	Optionality	Meaning
Single dash and perpendicular dash	One	Mandatory	Exactly one record is required
Single dash and circle	One	Optional	Zero or one record is allowed
Crow's foot and perpendicular dash	Many	Mandatory	Many records are required
Crow's foot and circle	Many	Optional	Zero or many records are allowed

Each relationship end combines a cardinality symbol with an optionality symbol.

How do the dashes, crow's feet, and circles show how many related rows can exist and whether a relationship is required?

The symbols at both ends must be interpreted together. One end tells you how many rows from one table can be associated with a row at the other end, while the opposite end gives the reverse perspective. This is why a complete relationship cannot be understood by looking at only one symbol.

Constructing a Table Relationship

1
Name the two tables that contain the related information.
2
Give each table a primary key, conventionally named id.
3
Add a foreign key in the table that needs to refer to the other table, using a name such as artist_id.
4
Draw a box for each table and list the table name and columns inside it.
5
Mark the primary key and foreign key columns so their roles are visible.
6
Connect the key columns with a relationship line.
7
Place the cardinality and optionality symbols at both ends of the line.
8
Read the completed diagram from each table's perspective.

Worked example

Building the Artist and Track relationship

Represent the source example in which an Artist table is connected to a Track table, and the Track table uses a foreign key to reference the Artist table.

1
Separate the entities
Create an Artist table for artist information and a Track table for track information.
2
Identify each table's primary key
Use id as the conventional primary key name in each table. The Artist id identifies an artist row, and the Track id identifies a track row.
3
Add the reference
Add artist_id to Track as the foreign key that stores an Artist primary key value.
4
Mark the diagram
Place PK beside the primary key columns and FK beside the foreign key column. Connect the Artist primary key to the Track foreign key.
5
Add relationship notation
Place the appropriate dash, crow's foot, and optionality symbols at both ends according to the relationship rules being modeled.

Result

The diagram shows two table boxes, identifies the key columns, and connects Artist.id with Track.artist_id. The keys show how rows link; the symbols show the relationship's cardinality and optionality.

How can a relationship between two tables be shown as connected key columns and then read as a statement about the data?

The three fundamental relationship types are one-to-one, one-to-many, and many-to-many. Their notation patterns differ because the symbols at the two ends describe the number of related rows from each perspective. The same diagram-reading process applies to all three: inspect both ends, identify cardinality, identify optionality, and then state the relationship in words.

Relationship type	What it describes
One-to-one	One row on one side relates to one row on the other side
One-to-many	One row on one side relates to many rows on the other side
Many-to-many	Many rows on one side relate to many rows on the other side

The notation at both ends of the relationship line makes these patterns recognizable.

Mistakes When Reading Keys and Symbols

Common mistake
Treating a foreign key as a completely new identifier for the related entity
What it looks like
Reading Track.artist_id as an unrelated label instead of as a stored Artist.id value
Why it fails
A foreign key stores a primary key value from another table. Its purpose is to point to an existing row in that table.
Do this instead
Follow the foreign key value into the referenced table's primary key column.
Common mistake
Reading only one end of a relationship line
What it looks like
Looking at the crow's foot but ignoring the circle or perpendicular dash
Why it fails
Cardinality and optionality are separate pieces of information, and both ends describe different perspectives.
Do this instead
Read the symbols at both ends, identifying the number of rows and whether the relationship is required or optional at each end.
Common mistake
Confusing a primary key with a foreign key
What it looks like
Assuming every column marked as a key identifies a row in its own table
Why it fails
A primary key uniquely identifies a row in its own table, while a foreign key stores a primary key value from another table.
Do this instead
Ask whether the column identifies the current table's row or links to a row in another table.
Common mistake
Assuming a circle means one
What it looks like
Interpreting a circle at a relationship end as exactly one related row
Why it fails
A circle indicates optionality, meaning zero is allowed. The dash or crow's foot supplies the one-versus-many information.
Do this instead
Combine the circle with the cardinality symbol: a circle with a single dash means zero or one, while a circle with a crow's foot means zero or many.

Practice the Trace

EASY

A Track row contains artist_id with the value 12. Explain the steps you would use to find the related Artist row, and state which column identifies that destination row.

Hints

Start with the column in the Track row.
Look for the same integer in the referenced table.
The destination column is the primary key.

Your attempt (not saved)

MEDIUM

A relationship end has a crow's foot and a circle. Interpret the two symbols together. Then compare that meaning with an end that has a single dash and a perpendicular dash.

Hints

The crow's foot describes cardinality.
The circle describes optionality.
The perpendicular dash describes a mandatory relationship.

Your attempt (not saved)

What do you think happens?

A table contains a column named artist_id. Before checking the diagram, what role is this name most likely signaling?

The table's own primary key
A foreign key referring to an Artist row
A relationship line
A table name

Reveal answer

Answer:

A foreign key referring to an Artist row

The table_name_id naming convention makes the referenced table explicit. In the source example, Track.artist_id stores an Artist primary key value.

Key Ideas to Retain

1
A Crow's Foot Diagram makes a complex data model visible by showing tables as boxes and relationships as lines.
2
A primary key is a unique integer identifier for a row in its own table, conventionally named id.
3
A foreign key stores a primary key value from another table, allowing one row to point to a related row.
4
A relationship line must be read from both ends: dashes and crow's feet show cardinality, while perpendicular dashes and circles show optionality.
5
To trace a relationship, read the foreign key value and find the row with the matching value in the referenced table's primary key column.

Key Takeaways

Crow's Foot Diagrams turn complex table relationships into a visual structure that can be read and discussed.
Primary keys uniquely identify rows within their own tables, while foreign keys store matching primary key values from other tables.
A foreign key link can be traced by matching its value to the referenced table's primary key.
Single dashes and crow's feet express one-versus-many cardinality; perpendicular dashes and circles express mandatory-versus-optional relationships.
A complete reading of a relationship requires examining both ends of the connecting line.

Related concepts

More concepts starting with P