SQL SELECT, INSERT, and UPDATE Statements
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.
From a Name to a Linked Record
Relational data is often divided across several tables. An artist belongs in Artist, an album belongs in Album, and a track belongs in Track. The challenge is that later rows need the primary key of an earlier row. The INSERT OR IGNORE followed by SELECT pattern solves this problem: insert the row if necessary, retrieve its primary key immediately, and pass that key into the next related insert.
A foreign key is a primary key value from one table stored in another table to establish a relationship between rows.
Tracing the Key-Recovery Pattern
Imagine processing one input record at a time. The program first extracts the artist name. It then performs INSERT OR IGNORE for the Artist row. This avoids treating an already-known artist as a new insertion. Immediately afterward, SELECT retrieves the primary key for that artist. The resulting artist_id is the concrete value needed when the Album row is inserted.
- Insert the Artist row with INSERT OR IGNORE.
- Select the primary key of that Artist row.
- Store the retrieved value as artist_id.
- Insert the Album row with artist_id as its foreign key.
- Select the Album primary key and store it as album_id.
- Insert the Track row with album_id as its foreign key.
Following Foreign-Key Dependencies
The insertion order is determined by dependency, not by preference. Album needs artist_id, so Artist must be available first. Track needs album_id, so Album must be available before Track. The required order is Artist, then Album, then Track.
Connecting the Three Tables
Each Album row stores an artist_id value. That value is the primary key of the Artist row to which the album belongs; it is not a copy of the artist name. Each Track row stores an album_id value. That value identifies the Album row for the track. As a result, a Track connects to an Album, and that Album connects to an Artist.
| Table | Role | Linking value |
|---|---|---|
| Artist | Stores the artist row | Its primary key is used by Album |
| Album | Stores the album row | artist_id links to Artist |
| Track | Stores the track row and metadata | album_id links to Album |
The key values that connect the three tables
Reconstructing Records with JOIN
Separating data across tables avoids duplication and helps maintain consistency, but it means that a complete record is not stored in one place. When you need a Track record together with its album title and artist name, a JOIN combines rows from the relevant tables. The JOIN matches primary-key and foreign-key values. For example, Album.artist_id can be matched with Artist.id so that an album row and its corresponding artist row appear together in the result.
Following One Related Record
A source record contains an artist, an album, and a track. Determine which key must be recovered at each stage.
Artist stage: Insert or preserve the Artist row, then SELECT its primary key. Store that value as artist_id.
Album stage: Insert the Album row with artist_id. Then SELECT the Album primary key and store it as album_id.
Track stage: Insert the Track row with album_id so the track is linked to its Album and, through that Album, to its Artist.
Reading stage: Use JOIN clauses when the complete track, album, and artist information must be viewed together.
The recovered keys form a chain: Artist primary key becomes artist_id, and Album primary key becomes album_id.
INSERT OR REPLACE for Track Metadata
The Track step differs from the Artist and Album pattern in one important way. Track uses INSERT OR REPLACE rather than INSERT OR IGNORE. This allows the code to update a Track's metadata, such as rating, count, and length, when the same track appears again in the CSV. The track still receives album_id, preserving its relationship to the Album and Artist tables.
| Statement role | Effect in this process | Use in the relationship chain |
|---|---|---|
| INSERT OR IGNORE | Inserts a row when needed without treating an existing row as a new insertion | Used for Artist and Album before retrieving their keys |
| SELECT | Retrieves an existing primary key | Provides artist_id or album_id for the next insert |
| INSERT OR REPLACE | Allows Track metadata to be updated when the same track appears again | Stores Track data together with album_id |
| JOIN | Combines related rows from multiple tables | Reconstructs a complete record for viewing |
Mistakes in Table Population
Inserting an Album before its Artist
Album depends on the Artist primary key through its artist_id foreign key.
Fix:
Insert or preserve the Artist first, then retrieve its primary key before inserting the Album.Inserting a Track before its Album
Track depends on the Album primary key through its album_id foreign key.
Fix:
Complete the Album insertion and key lookup before inserting the Track.Using an artist name where artist_id is required
The Album relationship is maintained by the Artist primary key value stored as artist_id.
Fix:
Retrieve the Artist primary key and use that value as artist_id.Stopping after INSERT OR IGNORE
The next related insert needs the concrete primary key value.
Fix:
Immediately SELECT and store the Artist primary key before continuing.Using INSERT OR IGNORE for repeated Track metadata
The Track step uses INSERT OR REPLACE so repeated appearances can update Track metadata.
Fix:
Use the Track pattern that permits the metadata to be updated while retaining album_id.
Apply the Dependency Chain
A processing step has identified an artist, an album, and a track. Write the sequence of database actions in the correct order using these terms: INSERT OR IGNORE, SELECT Artist primary key, insert Album with artist_id, SELECT Album primary key, insert Track with album_id, and JOIN for later display.
Hints
- Start with the table whose primary key is needed by another table.
- Retrieve each key immediately before the related insert that needs it.
- JOIN is for reconstructing records after the related rows exist, not for establishing the insertion order.
What do you think happens?
What should happen if the process tries to insert a Track before the Album primary key has been retrieved?
Reveal answer
Answer: The database can reject the insert because the required foreign-key dependency is missing
Track needs album_id, and album_id comes from the Album primary key. The Album must therefore be inserted and its key retrieved first.
Key Takeaways
- Use INSERT OR IGNORE followed immediately by SELECT to obtain the primary key needed by a related insert.
- Populate the tables in dependency order: Artist, then Album, then Track.
- Store primary key values as foreign keys to connect related rows; Album uses artist_id and Track uses album_id.
- Use INSERT OR REPLACE for Track when repeated input may require metadata such as rating, count, or length to be updated.
- Use JOIN clauses to combine separately stored Artist, Album, and Track data into a complete result.
Key Takeaways
- The INSERT OR IGNORE and SELECT pattern retrieves a primary key for relational linking.
- Foreign-key dependencies require Artist to be populated before Album, and Album before Track.
- artist_id links Album rows to Artist rows, while album_id links Track rows to Album rows.
- INSERT OR REPLACE allows repeated Track input to update Track metadata.
- JOIN clauses reconstruct complete records from data stored across multiple tables.