Primary Keys and Foreign Keys
Crow's Foot Diagrams use boxes to represent tables and lines with notation symbols to represent relationships between tables.
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.
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.
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?
Start in Track: Read the value stored in the Track row's artist_id foreign key column: 7.
Move to Artist: Use 7 as the value to search for in the Artist table's id primary key column.
Identify the row: The Artist row with id equal to 7 is the referenced row.
Use the related data: The other columns in that Artist row provide the artist information associated with the Track row.
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.
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
Primary key: a unique integer identifier that uniquely identifies each row within a table. It is conventionally named id.
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.
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.
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
- Name the two tables that contain the related information.
- Give each table a primary key, conventionally named id.
- Add a foreign key in the table that needs to refer to the other table, using a name such as artist_id.
- Draw a box for each table and list the table name and columns inside it.
- Mark the primary key and foreign key columns so their roles are visible.
- Connect the key columns with a relationship line.
- Place the cardinality and optionality symbols at both ends of the line.
- Read the completed diagram from each table's perspective.
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.
Separate the entities: Create an Artist table for artist information and a Track table for track information.
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.
Add the reference: Add artist_id to Track as the foreign key that stores an Artist primary key value.
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.
Add relationship notation: Place the appropriate dash, crow's foot, and optionality symbols at both ends according to the relationship rules being modeled.
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.
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
Treating a foreign key as a completely new identifier for the related entity
A foreign key stores a primary key value from another table. Its purpose is to point to an existing row in that table.
Fix:
Follow the foreign key value into the referenced table's primary key column.Reading only one end of a relationship line
Cardinality and optionality are separate pieces of information, and both ends describe different perspectives.
Fix:
Read the symbols at both ends, identifying the number of rows and whether the relationship is required or optional at each end.Confusing a primary key with a foreign key
A primary key uniquely identifies a row in its own table, while a foreign key stores a primary key value from another table.
Fix:
Ask whether the column identifies the current table's row or links to a row in another table.Assuming a circle means one
A circle indicates optionality, meaning zero is allowed. The dash or crow's foot supplies the one-versus-many information.
Fix:
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
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.
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.
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?
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
- A Crow's Foot Diagram makes a complex data model visible by showing tables as boxes and relationships as lines.
- A primary key is a unique integer identifier for a row in its own table, conventionally named id.
- A foreign key stores a primary key value from another table, allowing one row to point to a related row.
- A relationship line must be read from both ends: dashes and crow's feet show cardinality, while perpendicular dashes and circles show optionality.
- 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.