Concepts / Database Constraints and Unique Keys

Database Constraints and Unique Keys

Once the two entity primary keys have been retrieved, place them together in the junction table.

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

From One JSON Record to Three Tables

A many-to-many data-loading task begins with one structured JSON record but ends with data distributed across three database tables. The record contributes information to one entity table, information to a second entity table, and a relationship row to a junction table. The main challenge is preserving the connections between those rows without creating duplicate entities or duplicate relationships.

JSON recordentity A, entity B,relationshipEntity A tableentity A rowEntity B tableentity B rowJunction tableentity A key, entity B keyextract entity Aextract entity Bextract relationship
How does one JSON record become rows in two entity tables and one relationship table?

Reading Entities and Relationships

When parsing a JSON record, separate the information into two categories. Entity information describes the records that belong in the two entity tables. Relationship information describes how those two entities are connected. The entity values are inserted or located first because the junction table needs their primary keys. The relationship is then represented by storing those two keys together in the junction table.

JSON recordsource dataentity A fieldentity informationentity B fieldentity informationrelationship fieldconnection informationcontainscontainscontains
Which fields represent entities, and which field describes the relationship between them?
Worked example

Separating a Generated Record

A JSON record contains an entity A value, an entity B value, and information that the two entities are related. Determine what each part contributes to the database.

  1. 1
    Identify entity A

    The entity A value belongs in the first entity table, where it must have or receive a primary key.

  2. 2
    Identify entity B

    The entity B value belongs in the second entity table, where it must have or receive a primary key.

  3. 3
    Identify the relationship

    The relationship does not become a duplicate copy of either entity. It becomes a junction-table row that links the two primary keys.

Result

The JSON record supplies two entity values and one relationship. The relationship row can be created only after both entity primary keys are available.

Ensuring Entities Exist

For each entity, use INSERT OR IGNORE and then SELECT. INSERT OR IGNORE handles both cases: the entity may be new, or it may already exist. After that operation, SELECT retrieves the primary key for the entity that will be used in later work. This pattern avoids errors from attempting to insert an existing record while still giving the loader the key it needs.

Entity valueINSERT OR IGNOREensure record existsSELECTretrieve primary keyEntity primary keyuse in later operationsprocess valuenew or existing recordreturn key
What happens when an entity already exists, and how is its primary key retrieved?
Best practice

Retrieve the primary key immediately after INSERT OR IGNORE. Do not postpone the lookup until after processing other records, because the junction-table operation depends on having the correct key for each entity.

What do you think happens?

An entity already exists when INSERT OR IGNORE is applied. What should happen next?

  • Stop because the entity cannot be used again

  • Run SELECT to retrieve the existing entity's primary key

  • Create a second entity row with a new relationship

  • Insert the relationship before finding either key

Reveal answer
Answer:

Run SELECT to retrieve the existing entity's primary key

The INSERT OR IGNORE and SELECT pattern handles both new and existing records. SELECT supplies the primary key needed for subsequent operations.

Linking Keys in the Junction

Once the two entity primary keys have been retrieved, place them together in the junction table. One junction-table column stores the primary key from the first entity table, and another junction-table column stores the primary key from the second entity table. This pair represents the relationship extracted from the JSON record.

Entity Aprimary keyJunction rowentity A key, entity B keyEntity Bprimary keyentity A keyentity B key
How do the primary keys from two entity tables become foreign-key columns in the junction table?
Worked example

Building One Relationship Row

A parsed JSON record identifies entity A and entity B. Each entity has been processed with INSERT OR IGNORE followed by SELECT, and both primary keys are now available.

  1. 1
    Keep the first key

    Use the primary key retrieved for entity A as the first key value for the junction row.

  2. 2
    Keep the second key

    Use the primary key retrieved for entity B as the second key value for the junction row.

  3. 3
    Insert the pair

    Store the two keys together in the junction table so the relationship from the JSON record is represented in the database.

Result

The junction row links the two existing entity records through their primary keys.

Rejecting Duplicate Relationships

A unique constraint on the junction table prevents the same relationship from being stored repeatedly. The relevant identity is the pair of entity keys: when that pair is already represented, another attempt describes the same relationship rather than a new one. INSERT OR REPLACE is used for junction-table inserts to prevent duplicate relationships while maintaining idempotency.

Entity A keyUnique key pairentity A key + entity B keyOne relationshipjunction-table rowEntity B keyfirst keysecond keyrepresents
How does a unique constraint detect that the same pair of entities is already linked?
Key pairexisting relationshipOne junction rowpair stored onceKey pairrelationship remainsrepresentedOne junction rowpair stored oncestored asstored as
What changes in the junction table when an attempted insert conflicts with an existing unique key?

Loading Sequence Checklist

  1. 1
    Parse the JSON record and separate the two entity values from the relationship information.
  2. 2
    Apply INSERT OR IGNORE for the first entity.
  3. 3
    Immediately use SELECT to retrieve the first entity's primary key.
  4. 4
    Apply INSERT OR IGNORE for the second entity.
  5. 5
    Immediately use SELECT to retrieve the second entity's primary key.
  6. 6
    Use the two retrieved keys to identify the relationship in the junction table.
  7. 7
    Apply INSERT OR REPLACE to the junction table so the unique constraint prevents a duplicate relationship.
  • Common mistake

    Inserting directly into the junction table before retrieving entity keys

    What it looks like

    The loader identifies two entities but has not yet completed the SELECT operations.

    Why it fails

    The junction row must link the primary keys from both entity tables.

    Do this instead

    Process each entity with INSERT OR IGNORE followed immediately by SELECT, then create the relationship row.

  • Common mistake

    Treating an existing entity as an error

    What it looks like

    The loader assumes that an ignored insert means the entity cannot be used.

    Why it fails

    The pattern is designed to handle both new and existing records.

    Do this instead

    Run SELECT after INSERT OR IGNORE and use the existing record's primary key.

  • Common mistake

    Inserting the same relationship without a duplicate-prevention strategy

    What it looks like

    The same pair of entity keys is processed more than once.

    Why it fails

    Repeated processing can attempt to represent the same relationship again.

    Do this instead

    Use a unique constraint for the relationship key pair and INSERT OR REPLACE for junction-table inserts.

Practice the Loading Logic

MEDIUM

A JSON record identifies entity A and entity B, and the relationship between them has already been seen once. Describe the correct processing order. Include where INSERT OR IGNORE, SELECT, and INSERT OR REPLACE belong, and explain why the second relationship attempt does not create a duplicate junction-table entry.

Hints
  • The two entity keys must be available before the junction-table operation.
  • SELECT follows INSERT OR IGNORE for each entity.
  • The unique constraint applies to the pair of entity keys in the junction table.
Worked example

Checking the Complete Pattern

A record contains two entities and their relationship. The first entity is new, the second already exists, and the relationship has been loaded previously.

  1. 1
    Process the first entity

    Use INSERT OR IGNORE, then SELECT its primary key after the operation.

  2. 2
    Process the second entity

    Use INSERT OR IGNORE even though the entity already exists, then SELECT its existing primary key.

  3. 3
    Prepare the relationship

    Combine the two retrieved primary keys as the junction-table key pair.

  4. 4
    Apply the junction operation

    Use INSERT OR REPLACE. The unique constraint recognizes that the pair already represents a relationship, so the relationship is not duplicated.

Result

The entity tables contain the required records, both primary keys are available, and the junction table continues to represent the relationship without a duplicate entry.

Reliable Many-to-Many Loading

  1. 1
    Parse each JSON record into two entity values and one relationship.
  2. 2
    Use INSERT OR IGNORE followed immediately by SELECT for each entity so both new and existing records yield usable primary keys.
  3. 3
    Build the junction-table row from the two retrieved entity primary keys.
  4. 4
    Use a unique constraint and INSERT OR REPLACE to prevent duplicate relationships and support idempotent loading.

Key Takeaways

  • Many-to-many JSON data is distributed across two entity tables and one junction table.
  • INSERT OR IGNORE followed by SELECT ensures that an entity exists and provides its primary key whether the entity is new or already present.
  • The junction table links the two entities by storing their primary keys together.
  • A unique constraint identifies an existing relationship, while INSERT OR REPLACE prevents duplicate junction-table entries.

Related Topics

Related concepts

More concepts starting with D