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.

Programming

Interactive lab

Try it: Normalising into Lookup Tables

How normalisation removes repeated text: each distinct artist or album is stored once in a lookup table with an integer id (INSERT OR IGNORE, then SELECT id), the other table stores only that id as a foreign key, and a JOIN rebuilds the original rows. A roster preset links users and courses through a junction table.

How it works

For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
SELECT the text's id back — new or existing.
Store that integer id (a foreign key) in the next table instead of the text.
Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
A JOIN that follows the ids rebuilds the same rows, while each distinct string is now stored once.

Default run (27 steps): A flat table: 6 rows × 3 text columns = 18 stored strings, many of them repeated. Split it into Artist, Album and Track. … Done: 18 stored strings became 12 (179 → 130 characters). The JOIN rebuilt 6 rows, the same rows as the flat table.

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Follows the Python for Everybody tracks and roster programs on at most 8 rows; the stored-string count ignores the (small) space integers take.

Educational simulation

Loading the simulation…

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.

Key point

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.

How does a row move from INSERT OR IGNORE to SELECT, and how is the retrieved primary key passed into a related insert?

1
Insert the Artist row with INSERT OR IGNORE.
2
Select the primary key of that Artist row.
3
Store the retrieved value as artist_id.
4
Insert the Album row with artist_id as its foreign key.
5
Select the Album primary key and store it as album_id.
6
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.

Which table must be populated first, and how do foreign-key dependencies determine the order of Artist, Album, and Track inserts?

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.

What does each table contain, which keys connect the tables, and how does an artist relate to albums and tracks?

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.

How do rows from Artist, Album, and Track combine through JOIN clauses to reconstruct one complete record?

Worked example

Following One Related Record

A source record contains an artist, an album, and a track. Determine which key must be recovered at each stage.

1
Artist stage
Insert or preserve the Artist row, then SELECT its primary key. Store that value as artist_id.
2
Album stage
Insert the Album row with artist_id. Then SELECT the Album primary key and store it as album_id.
3
Track stage
Insert the Track row with album_id so the track is linked to its Album and, through that Album, to its Artist.
4
Reading stage
Use JOIN clauses when the complete track, album, and artist information must be viewed together.

Result

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

The distinct roles of the statements in the relational workflow

Mistakes in Table Population

Common mistake
Inserting an Album before its Artist
What it looks like
Attempting to create an Album without first having the required artist_id
Why it fails
Album depends on the Artist primary key through its artist_id foreign key.
Do this instead
Insert or preserve the Artist first, then retrieve its primary key before inserting the Album.
Common mistake
Inserting a Track before its Album
What it looks like
Attempting to create a Track without a recovered album_id
Why it fails
Track depends on the Album primary key through its album_id foreign key.
Do this instead
Complete the Album insertion and key lookup before inserting the Track.
Common mistake
Using an artist name where artist_id is required
What it looks like
Treating the Album relationship value as a copy of the artist name
Why it fails
The Album relationship is maintained by the Artist primary key value stored as artist_id.
Do this instead
Retrieve the Artist primary key and use that value as artist_id.
Common mistake
Stopping after INSERT OR IGNORE
What it looks like
Inserting the Artist and then trying to create the Album without selecting the Artist key
Why it fails
The next related insert needs the concrete primary key value.
Do this instead
Immediately SELECT and store the Artist primary key before continuing.
Common mistake
Using INSERT OR IGNORE for repeated Track metadata
What it looks like
A track appears again with a changed rating, count, or length, but the existing metadata is left unchanged
Why it fails
The Track step uses INSERT OR REPLACE so repeated appearances can update Track metadata.
Do this instead
Use the Track pattern that permits the metadata to be updated while retaining album_id.

Apply the Dependency Chain

MEDIUM

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.

Your attempt (not saved)

What do you think happens?

What should happen if the process tries to insert a Track before the Album primary key has been retrieved?

The Track can be linked automatically without a key
The database can reject the insert because the required foreign-key dependency is missing
The Artist primary key will be used instead
The JOIN will create the missing Album

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

1
Use INSERT OR IGNORE followed immediately by SELECT to obtain the primary key needed by a related insert.
2
Populate the tables in dependency order: Artist, then Album, then Track.
3
Store primary key values as foreign keys to connect related rows; Album uses artist_id and Track uses album_id.
4
Use INSERT OR REPLACE for Track when repeated input may require metadata such as rating, count, or length to be updated.
5
Use JOIN clauses to combine separately stored Artist, Album, and Track data into a complete result.

At a glance

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.

Related concepts

More concepts starting with S