Concepts / Transaction Management and Rollback

Transaction Management and Rollback

User registration, tag or category management, and reference-data linking are examples of workflows where an existing record can be reused or a missing record can be created.

  • Programming

A Duplicate That Need Not Crash

Suppose an application receives a username that may already be stored. A normal insertion that violates a constraint raises an error and rejects the operation. That behavior protects the database from invalid data, but it may not match the application's goal. Sometimes the correct response is to reuse the existing record and continue. SQLite's INSERT OR IGNORE directive supports this behavior: if the attempted insertion would violate a constraint, the insertion is silently skipped and the program continues.

Key point

INSERT OR IGNORE has two possible outcomes: a valid new row is inserted, or an insertion that would violate a constraint is quietly skipped.

Tracing the Two Outcomes

INSERT OR IGNOREattempt insertionNew rowno violationInserted rowtable changesConstraint violationduplicate or otherviolationSkipped insertionprogram continuesevaluate constraintsnoyesignore
What happens when the attempted insertion would violate a constraint?

The important distinction is between the insertion attempt and the final table state. INSERT OR IGNORE does not update an existing row when a conflict occurs. It leaves the attempted row out of the table. If no constraint would be violated, the new row is added normally.

What do you think happens?

A table already contains the unique username sam. What should happen when INSERT OR IGNORE attempts to add sam again?

  • A second row is added

  • The existing row is deleted

  • The attempted insertion is skipped and the program continues

  • The existing row is replaced

Reveal answer
Answer:

The attempted insertion is skipped and the program continues

The duplicate violates the uniqueness constraint, so INSERT OR IGNORE silently ignores that insertion. The existing row remains.

Why Uniqueness Matters

The duplicate-handling behavior depends on a constraint that can identify a conflict. A UNIQUE constraint restricts duplicate values in one or more columns. If a username column is UNIQUE, two rows cannot have the same username, so attempting to insert an existing username creates a constraint violation that INSERT OR IGNORE can skip.

Column situationAttempted duplicateResult with INSERT OR IGNORE
A UNIQUE constraint is presentThe value violates the constraintThe insertion is skipped
No UNIQUE constraint is presentThe value is allowed to repeatThe row is inserted, assuming no other constraint is violated

A duplicate value is skipped only when it conflicts with a constraint.

UNIQUE usernameduplicate conflictsSkipped insertionexisting row remainsOrdinary usernameduplicate allowedInserted rowassuming no other violationduplicate valueduplicate value
How does the presence or absence of a UNIQUE constraint change duplicate handling?

Finding the Record ID

Skipping a duplicate is useful, but an application often needs the record's primary key afterward. The practical pattern is a two-step sequence: first attempt the insertion with INSERT OR IGNORE, then run SELECT to retrieve the primary key for the unique value. If the value was new, SELECT finds the row just inserted. If the value already existed, SELECT finds that existing row.

Code walkthrough
INSERT OR IGNORE INTO users (username)
VALUES ('sam');

SELECT user_id
FROM users
WHERE username = 'sam';
  1. 1
    The INSERT OR IGNORE statement attempts to add sam.
  2. 2
    If sam is already present under a UNIQUE constraint, the insertion is skipped without an error.
  3. 3
    The SELECT then searches for sam and retrieves its user_id.
  4. 4
    The unique value makes this lookup reliable because it maps to exactly one row.
  • The first statement attempts to ensure that sam exists.
  • The second statement retrieves the primary key associated with sam.
Output
The SELECT returns the user_id for sam, whether the row was newly inserted or was already present.
ApplicationINSERT OR IGNOREensure usernameusers tablenew or existing rowSELECT user_idfind usernameuser_idreturned to applicationattemptinsert or skipcontinuereturn primary key
How does the process move from attempting the insertion to selecting the existing or newly created primary key?
Before

users contains: sam

The existing sam row is present.

After

INSERT OR IGNORE attempts sam again

The table still contains one sam row; the duplicate insertion is ignored.

The SELECT follow-up is reliable only when the value used in the search is unique. If several rows could share that value, the SELECT would not identify one specific record. This is why the UNIQUE constraint is central to the pattern rather than an optional detail.

Choosing the Pattern Carefully

Use this pattern when the application needs to ensure that a record exists and then use its primary key. User registration, tag or category management, and reference-data linking are examples of workflows where an existing record can be reused or a missing record can be created.

Do not choose this pattern when the application must know whether the insertion actually occurred. INSERT OR IGNORE intentionally hides the constraint-violation error by skipping the insertion. If later logic depends on distinguishing a newly created row from an ignored duplicate, a different approach is needed, such as checking existence before insertion or using a more complex conditional statement.

  • Common mistake

    Assuming every repeated value will be ignored.

    What it looks like

    A column such as eye_color allows multiple rows to contain Blue because it has no UNIQUE constraint.

    Why it fails

    INSERT OR IGNORE only skips an insertion when the new row would violate a constraint.

    Do this instead

    Identify the UNIQUE constraint or another relevant constraint before relying on duplicate handling.

  • Common mistake

    Using SELECT after INSERT OR IGNORE without a unique lookup value.

    What it looks like

    Searching for a value in a column that permits several identical rows.

    Why it fails

    The SELECT may not identify one specific record when the value maps to multiple rows.

    Do this instead

    Use a value protected by a UNIQUE constraint when the goal is to retrieve one record's primary key.

  • Common mistake

    Treating an ignored insertion as an update.

    What it looks like

    Attempting to insert an existing username with different accompanying data.

    Why it fails

    The conflicting insertion is skipped; INSERT OR IGNORE does not replace the existing row.

    Do this instead

    Use this pattern only when reusing the existing record is the intended behavior.

  • Common mistake

    Using INSERT OR IGNORE when the program must report a duplicate.

    What it looks like

    A workflow requires different behavior for a newly registered username and an already registered username.

    Why it fails

    The pattern is designed to continue without raising an error for the violation.

    Do this instead

    Use an approach that explicitly checks or distinguishes the insertion result.

Apply the Workflow

EASY

A tags table stores a tag name under a UNIQUE constraint. The application receives the tag name python, but it does not know whether that tag already exists. Describe the two database actions you would use to ensure the tag exists and obtain its primary key.

Hints
  • The first action should attempt the insertion without allowing a duplicate to raise an error.
  • The second action should search by the unique tag name and return the tag's primary key.
  • Explain why the UNIQUE constraint makes the lookup reliable.
Worked example

Reusing a tag or creating it

An application needs the primary key for the tag named news, whether news is already stored or must be added.

  1. 1
    Attempt the insertion

    Use INSERT OR IGNORE with news. A missing tag can be inserted, while an existing tag protected by UNIQUE causes the insertion to be skipped.

  2. 2
    Search for the tag

    Use SELECT with the unique tag value news to retrieve its primary key.

  3. 3
    Use the returned key

    The application can use the primary key to link another record to the tag.

Result

The workflow continues with the primary key for the newly inserted or already existing news tag.

Essential Takeaways

  1. 1
    INSERT OR IGNORE silently skips an insertion that would violate a constraint and lets the program continue.
  2. 2
    A successful insertion changes the table; an ignored duplicate leaves the existing table row in place.
  3. 3
    A UNIQUE constraint is what makes duplicate handling meaningful and makes a follow-up SELECT reliable for one primary key.
  4. 4
    The two-step pattern is INSERT OR IGNORE followed by SELECT for workflows that need a record to exist and need its primary key.
  5. 5
    Use another approach when the application must know whether a new row was inserted rather than merely ensuring that a record exists.

At a glance

  • INSERT OR IGNORE prevents a constraint violation from interrupting the program by skipping the conflicting insertion.
  • Pairing it with SELECT lets an application retrieve the primary key of either a newly created or already existing record.
  • The pattern depends on a UNIQUE constraint when duplicate values must map to exactly one row.
  • Without a uniqueness constraint, repeated values are normally accepted unless another constraint is violated.
  • This approach is best when the application needs a record to exist, not when it must distinguish a new insertion from an ignored duplicate.

Related Topics

Related concepts

More concepts starting with T