Concepts / Primary Keys and Unique Constraints

Primary Keys and Unique Constraints

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.

  • Programming
Interactive lab

Try it: Keys and Constraints

How SQLite enforces PRIMARY KEY, UNIQUE, NOT NULL and FOREIGN KEY constraints on INSERT and DELETE, how an INTEGER PRIMARY KEY assigns ids (with and without AUTOINCREMENT), what INSERT OR IGNORE skips, and what ON DELETE RESTRICT, CASCADE and SET NULL do to child rows.

How it works

  1. INSERT: a NULL id is replaced by max(id) + 1 — or, with AUTOINCREMENT, by one more than the largest id ever used — and last_insert_rowid() reports it.
  2. Constraints are checked in order: NOT NULL, then the PRIMARY KEY, then UNIQUE, then the FOREIGN KEY (the parent row must exist). The first failure aborts the statement with SQLite's error message.
  3. INSERT OR IGNORE silently skips a row that breaks NOT NULL, PRIMARY KEY or UNIQUE — but a foreign-key failure is still an error.
  4. DELETE of a parent row that has children: RESTRICT refuses, CASCADE deletes the children too, SET NULL clears their foreign key. None of this happens unless PRAGMA foreign_keys = ON (SQLite's default is OFF).

Default run (5 steps): 3 artists and 3 albums. PRAGMA foreign_keys = ON, ON DELETE RESTRICT. Choose an operation. Example: INSERT INTO artist (name) VALUES ('Queen'). … Error: UNIQUE constraint failed: artist.name. Nothing was changed (the statement is rolled back).

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). Two fixed tables of at most 8 rows and ids up to 99; changing the schema recreates the tables with the starting rows. Error texts and ids were checked step by step against sqlite3.

Educational simulation

Loading the simulation…

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

  1. For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
  2. SELECT the text's id back — new or existing.
  3. Store that integer id (a foreign key) in the next table instead of the text.
  4. Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
  5. 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…

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.

Key point

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.

INSERT OR IGNOREArtistartist nameSELECT artist keyartist_idINSERT Albumuses artist_idthenkey becomes foreign key
What happens first when a row may already exist, and how does the next SELECT retrieve the key needed for a 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?

  • The artist primary key

  • The track primary key

  • The album primary key

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.

Worked example

From Artist Name to Track Link

Trace the key values needed to connect one artist, one album, and one track.

  1. 1
    Read the input

    The program reads a CSV file line by line, splits each line into fields, and extracts the artist name.

  2. 2
    Handle the Artist

    The program executes INSERT OR IGNORE for the Artist and then immediately retrieves that Artist row's primary key.

  3. 3
    Store artist_id

    The retrieved primary key is stored as artist_id. This is the concrete value required when inserting the related Album.

  4. 4
    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.

  5. 5
    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.

Result

The relationship is built in two links: Track points to Album through album_id, and Album points to Artist through artist_id.

artist_idArtist primary keyAlbumstores artist_idalbum_idAlbum primary keyTrackstores album_idforeign key valueretrieve primary keyforeign key value
Which primary key is retrieved at each stage, and where is that value used next?

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.

Artistcreates artist_idAlbumneeds artist_idTrackneeds album_idartist_idalbum_id
Which table must be populated first, and how does the required order flow from Artist to Album to Track?
TableKey used from an earlier tableWhy it must occur at this stage
ArtistNone in this chainIts primary key is needed by Album
Albumartist_idIts primary key is needed by Track
Trackalbum_idIt completes the chain to Album and Artist

The dependency chain determines the population order.

Primary Keys and Repeated Rows

Definition

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.

IdeaRole in this workflow
Primary keyThe row value retrieved after insertion or recognition and passed into a related table
Foreign keyA primary key value from one table stored in another table to establish a relationship
INSERT OR IGNOREThe first step when a row may already exist
SELECTThe immediate second step that retrieves the primary key for later linking

The supplied workflow emphasizes how keys connect tables and how the two-step pattern retrieves them.

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.

Artistid, nameAlbumid, artist_id, titleTrackalbum_id, metadataComplete track recordtrack, album, artistArtist.id matches Album.artist_idAlbum.id matches Track.album_idtrack row
How do rows from Artist, Album, and Track connect and combine into one complete record?
Worked example

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.

  1. 1
    Start with Track

    The Track row contains album_id, which identifies the related Album row.

  2. 2
    Match the Album

    The JOIN matches Track.album_id with the Album primary key. This supplies the Album row and its title.

  3. 3
    Follow artist_id

    The Album row contains artist_id. A second JOIN matches Album.artist_id with Artist.id.

  4. 4
    Return combined data

    The result can contain the Track information together with the Album title and Artist name.

Result

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

  • Common mistake

    Inserting the Album before the Artist

    What it looks like

    The Album insert has no valid artist_id because the Artist row has not been inserted or found yet.

    Why it fails

    Album depends on the Artist primary key through its artist_id foreign key.

    Do this instead

    Insert or ignore the Artist first, select its primary key, and use that value in the Album insert.

  • Common mistake

    Inserting the Track before the Album

    What it looks like

    The Track insert is attempted before album_id has been retrieved.

    Why it fails

    Track depends on the Album primary key through album_id.

    Do this instead

    Complete the Album insert and select its primary key before inserting the Track.

  • Common mistake

    Using a name instead of the key value

    What it looks like

    The Album stores an artist name rather than the Artist row's primary key.

    Why it fails

    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.

    Do this instead

    Store the Artist primary key in artist_id.

  • Common mistake

    Stopping after INSERT OR IGNORE

    What it looks like

    The program inserts or recognizes the Artist but never selects its primary key.

    Why it fails

    The next Album insert still lacks the concrete artist_id needed for linking.

    Do this instead

    Treat INSERT OR IGNORE and SELECT as one two-step pattern.

Practice the Dependency Chain

MEDIUM

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.
MEDIUM

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

  1. 1
    Use INSERT OR IGNORE followed immediately by SELECT when you need the primary key of a row that may already exist.
  2. 2
    Populate the tables in dependency order: Artist, then Album, then Track.
  3. 3
    Store the Artist primary key as Album.artist_id and the Album primary key as Track.album_id.
  4. 4
    Foreign keys connect rows by storing primary key values from related tables.
  5. 5
    Use JOIN clauses to combine the separate Artist, Album, and Track data into a complete record.

At a glance

  • 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.

Related Topics

Related concepts

More concepts starting with P