Concepts / INSERT Statement Syntax and Options

INSERT Statement Syntax and Options

When inserting a row, the important choice for a PRIMARY KEY column is whether to supply a value or omit the column.

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

Why Separate Roles

Suppose many Member rows need to record roles such as administrator, editor, or reviewer. Role normalization moves role information into a dedicated Role table instead of repeating that information throughout the Member table. The Member table then stores a role_id value that points to the appropriate Role record.

Member row 1role: editorRole tableid, nameMember row 2role: editorMember tablerole_idrole_id references id
What information moves from Member into Role, and how does the separation reduce repeated role data?

Role Table Constraints

The normalized design gives the Role table two important constraints. Its id column is the PRIMARY KEY, identifying each Role record. Its name column is UNIQUE, so role names cannot be repeated. The Member table does not store the role name as its link; it uses role_id to reference the Role table's id column.

TableColumnConstraint or role
RoleidPRIMARY KEY
RolenameUNIQUE
Memberrole_idForeign key referencing Role.id

The columns and constraints used to centralize role information

Key point

PRIMARY KEY and UNIQUE both restrict duplication, but they apply to different columns in this design: the Role id column is the primary identifier, while the Role name column must also remain unique.

Member-to-Role Linking

A Member row points to a Role row through role_id. The value in Member.role_id must identify an existing Role record. This is the purpose of the foreign key constraint: it enforces referential integrity and prevents a Member row from referring to a Role that does not exist.

Member.role_idrole referenceRole.idPRIMARY KEYMissing Rolereference rejectedforeign key referencenon-existent id
How does a Member row point to its Role row, and what happens when the referenced role does not exist?
Worked example

Connecting a member to an existing role

A Member must be assigned the Role record whose id represents the editor role.

  1. 1
    Find the Role record

    Use the existing Role record identified by its id. The Role table centralizes the role information.

  2. 2
    Store the identifier

    Insert the corresponding identifier into the Member row's role_id column.

  3. 3
    Check the reference

    The foreign key requires the referenced Role record to exist. A Member row cannot point to a non-existent Role record.

Result

The Member row is linked to the centralized Role record through role_id.

Choosing INSERT Columns

When inserting a row, the important choice for a PRIMARY KEY column is whether to supply a value or omit the column. If you omit the PRIMARY KEY value, the database can generate it. If you include the column, you are manually assigning its value, and that value must satisfy the table's constraints.

Role.nameeditorGenerated iddatabase suppliedRole.idomittedinserted with role datadatabase generates
When the database generates the PRIMARY KEY, which value is supplied by the insertion and which value is generated by the database?
Worked example

Inserting a Role without choosing its id

Add a new Role named editor while allowing the database to generate the Role id.

  1. 1
    Omit the PRIMARY KEY column

    The insertion supplies the role information but does not manually assign Role.id.

  2. 2
    Allow generation

    Because the PRIMARY KEY value is omitted, the database can generate it.

  3. 3
    Use the generated identifier

    The resulting Role identifier is the value that a related Member row can use in role_id.

Result

A Role record is added without manually choosing its PRIMARY KEY value.

Manual Primary Keys

A PRIMARY KEY value may also be assigned manually during insertion. The assigned value must be unique and must not already be present in the table. If it duplicates an existing PRIMARY KEY value, the insertion violates the PRIMARY KEY constraint. Manual assignment is therefore an option with a restriction, not permission to reuse any identifier.

New idnot presentRole rowacceptedExisting idalready presentConstraint violationPRIMARY KEYunique assignmentduplicate assignment
What happens when a manually supplied PRIMARY KEY is new or duplicates an existing value?

What do you think happens?

A Role table already contains a row with a particular id. What should happen if another insertion manually assigns that same id?

  • The existing row is automatically replaced

  • The new row is accepted with a duplicate PRIMARY KEY

  • The insertion violates the PRIMARY KEY constraint

Reveal answer
Answer:

The insertion violates the PRIMARY KEY constraint.

The source requires manually assigned PRIMARY KEY values to be unique and not already present.

Mistakes with Insertions

  • Common mistake

    Repeating role names in Member rows instead of centralizing them in Role.

    What it looks like

    Several Member rows independently store the same role information.

    Why it fails

    The normalized design is intended to separate role information into a dedicated Role table and eliminate redundancy.

    Do this instead

    Store the role once in Role and connect each Member through role_id.

  • Common mistake

    Using a role_id that does not identify an existing Role record.

    What it looks like

    A Member insertion refers to a Role identifier that is not present in Role.

    Why it fails

    The foreign key enforces referential integrity and prevents references to non-existent Role records.

    Do this instead

    Reference an existing Role record.

  • Common mistake

    Manually assigning a PRIMARY KEY value that already exists.

    What it looks like

    A new Role insertion uses an id already assigned to another Role row.

    Why it fails

    PRIMARY KEY values must be unique and not already present.

    Do this instead

    Use a new unique value or omit the PRIMARY KEY so the database can generate it.

  • Common mistake

    Assuming that every insertion must manually provide a PRIMARY KEY.

    What it looks like

    The insertion always supplies Role.id even though automatic generation is available.

    Why it fails

    PRIMARY KEY values can be omitted to allow database generation.

    Do this instead

    Omit the PRIMARY KEY column when you want the database to generate its value.

Practice Decisions

MEDIUM

For each situation, decide whether the insertion should manually assign the PRIMARY KEY or omit it: adding a new Role when the database should generate identifiers; adding a Role with a deliberately chosen unused identifier; adding a Role using an identifier already present; and adding a Member whose role_id points to no existing Role.

Hints
  • Omitting the PRIMARY KEY allows database generation.
  • A manually assigned PRIMARY KEY must be unique and not already present.
  • A Member foreign key must reference an existing Role record.
Worked example

Checking four insertion choices

Evaluate the four practice situations using the Role and Member constraints.

  1. 1
    Generated Role identifier

    Omit the Role PRIMARY KEY value so the database can generate it.

  2. 2
    Unused manual identifier

    Manual assignment is allowed when the chosen PRIMARY KEY value is unique and not already present.

  3. 3
    Existing manual identifier

    Reject the choice because a duplicate PRIMARY KEY value violates the constraint.

  4. 4
    Missing referenced Role

    Reject the Member reference because the foreign key cannot point to a non-existent Role record.

Result

The valid choices depend on both insertion behavior and the constraints protecting Role and Member.

Key Takeaways

  1. 1
    Role normalization places role information in a dedicated Role table, reducing redundancy and centralizing role management.
  2. 2
    Role.id is the PRIMARY KEY, Role.name is UNIQUE, and Member.role_id references Role.id through a foreign key.
  3. 3
    A PRIMARY KEY value can be omitted during insertion so the database can generate it.
  4. 4
    A manually assigned PRIMARY KEY must be unique and must not already exist.
  5. 5
    Foreign key enforcement prevents Member rows from referring to non-existent Role records.

At a glance

  • Normalize repeated role information by creating a separate Role table.
  • Use Role.id as the PRIMARY KEY and Role.name as a UNIQUE column.
  • Link Member to Role with the Member.role_id foreign key.
  • Omit a PRIMARY KEY value when the database should generate it.
  • Manually supplied PRIMARY KEY values must be unique and not already present.

Related Topics

Related concepts

More concepts starting with I