Primary Keys and Unique Constraints
The INSERT OR IGNORE and SELECT pattern is a two-step sequence that inserts a row and immediately retrieves its primary key for use in subsequent inserts.
The Linking Problem
Suppose a program reads music records from a CSV file. Each record contains an artist, an album, and a track. The database stores these details in separate tables, so the program must do more than insert text. It must discover the primary key for the artist, use that value when inserting the album, then discover the album's primary key before inserting the track.
The central pattern is a two-step sequence: INSERT OR IGNORE a row, then SELECT its primary key. That retrieved key becomes the foreign key used by the next related insert.
Following One Record
What do you think happens?
A CSV line contains an artist, an album, and a track. Which key must the program obtain before it can insert the album?
Reveal answer
Answer: The artist primary key
The Album row contains an artist_id foreign key. The program therefore inserts or finds the Artist first, retrieves its primary key, and uses that value in the Album insert.
From Artist Name to Track Link
Trace the key values needed to connect one artist, one album, and one track.
Read the input: The program reads a CSV file line by line, splits each line into fields, and extracts the artist name.
Handle the Artist: The program executes INSERT OR IGNORE for the Artist and then immediately retrieves that Artist row's primary key.
Store artist_id: The retrieved primary key is stored as artist_id. This is the concrete value required when inserting the related Album.
Handle the Album: The program inserts the Album with artist_id as its foreign key, then selects and stores the Album's primary key as album_id.
Handle the Track: The Track insert uses album_id, linking the Track to its Album. The Album already links to the Artist through artist_id.
The relationship is built in two links: Track points to Album through album_id, and Album points to Artist through artist_id.
Dependency-Driven Insertion
The insertion order is determined by foreign key dependencies, not by preference. Artist must come first because Album needs artist_id. Album must come second because Track needs album_id. Track comes last because its relationship can be completed only after the Album key has been retrieved.
| Table | Key used from an earlier table | Why it must occur at this stage |
|---|---|---|
| Artist | None in this chain | Its primary key is needed by Album |
| Album | artist_id | Its primary key is needed by Track |
| Track | album_id | It completes the chain to Album and Artist |
Primary Keys and Repeated Rows
A primary key is the value used to identify a row for relational linking. In this workflow, the Artist primary key is retrieved and stored as artist_id, while the Album primary key is retrieved and stored as album_id. Those values are then stored in related tables as foreign keys.
INSERT OR IGNORE followed by SELECT is useful when the row may already exist. The insert step attempts to place the row in the table while ignoring an existing matching row. The following SELECT retrieves the primary key of the row that should be used for the next relationship. The source material presents this as a two-step pattern for obtaining the correct key before a related insert.
| Idea | Role in this workflow |
|---|---|
| Primary key | The row value retrieved after insertion or recognition and passed into a related table |
| Foreign key | A primary key value from one table stored in another table to establish a relationship |
| INSERT OR IGNORE | The first step when a row may already exist |
| SELECT | The immediate second step that retrieves the primary key for later linking |
Joining the Stored Data
The three tables are stored separately to avoid duplication and maintain consistency. A complete music record is therefore not necessarily stored as one row. When the program needs a Track together with its Album title and Artist name, a JOIN combines rows from the separate tables.
A JOIN matches primary and foreign key values. For example, Album.artist_id = Artist.id connects each Album row to its corresponding Artist row. Extending the same relationship through Track.album_id connects a Track to its Album and then to the Artist associated with that Album.
Following a JOIN Across Three Tables
Explain how a complete Track record can include the track data, its album title, and its artist name even though the information is stored in separate tables.
Start with Track: The Track row contains album_id, which identifies the related Album row.
Match the Album: The JOIN matches Track.album_id with the Album primary key. This supplies the Album row and its title.
Follow artist_id: The Album row contains artist_id. A second JOIN matches Album.artist_id with Artist.id.
Return combined data: The result can contain the Track information together with the Album title and Artist name.
JOIN reconstructs a complete view by matching the stored primary and foreign key values.
Track Updates and Existing Data
The source workflow treats Track differently from Artist and Album. Track uses INSERT OR REPLACE rather than INSERT OR IGNORE. This permits the code to update Track metadata such as rating, count, and length when the same track appears again in the CSV.
Common Ordering Mistakes
Inserting the Album before the Artist
Album depends on the Artist primary key through its artist_id foreign key.
Fix:
Insert or ignore the Artist first, select its primary key, and use that value in the Album insert.Inserting the Track before the Album
Track depends on the Album primary key through album_id.
Fix:
Complete the Album insert and select its primary key before inserting the Track.Using a name instead of the key value
The described relationship is maintained by matching primary and foreign key values. The Album's artist_id is not a copy of the artist's name.
Fix:
Store the Artist primary key in artist_id.Stopping after INSERT OR IGNORE
The next Album insert still lacks the concrete artist_id needed for linking.
Fix:
Treat INSERT OR IGNORE and SELECT as one two-step pattern.
Practice the Dependency Chain
A program has just read one CSV line containing an artist name, an album title, and a track. Write the sequence of database actions in the correct order. Then state which key is stored in Album and which key is stored in Track.
Hints
- Begin with the table whose primary key is needed by Album.
- After each Artist or Album insert, retrieve the row's primary key before moving on.
- Album stores artist_id, while Track stores album_id.
Explain how a JOIN can return a Track together with its Album title and Artist name. Identify both key matches that the database follows.
Hints
- Start with Track.album_id.
- Then follow the Album row's artist_id.
- The source relationship matches Album.artist_id with Artist.id.
Key Takeaways
- Use INSERT OR IGNORE followed immediately by SELECT when you need the primary key of a row that may already exist.
- Populate the tables in dependency order: Artist, then Album, then Track.
- Store the Artist primary key as Album.artist_id and the Album primary key as Track.album_id.
- Foreign keys connect rows by storing primary key values from related tables.
- Use JOIN clauses to combine the separate Artist, Album, and Track data into a complete record.
Key Takeaways
- The INSERT OR IGNORE followed by SELECT pattern retrieves the primary key needed for the next relational insert.
- Foreign key dependencies require the population order Artist, Album, then Track.
- Album stores artist_id, and Track stores album_id, creating a chain of relationships.
- JOIN clauses follow those key matches to reconstruct complete records from separate tables.
- Track uses INSERT OR REPLACE in the described workflow when repeated CSV data should update track metadata.