Transaction Management and Rollback

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.

Tracing the Two Outcomes

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.

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.

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.

INSERT OR IGNORE INTO users (username) VALUES ('sam'); SELECT user_id FROM users WHERE username = 'sam';

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.

• Assuming every repeated value will be ignored.

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

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

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

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

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

• Treating an ignored insertion as an update.

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

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

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

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

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

Apply the Workflow

Essential Takeaways

1. INSERT OR IGNORE silently skips an insertion that would violate a constraint and lets the program continue.
2. A successful insertion changes the table; an ignored duplicate leaves the existing table row in place.
3. A UNIQUE constraint is what makes duplicate handling meaningful and makes a follow-up SELECT reliable for one primary key.
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. Use another approach when the application must know whether a new row was inserted rather than merely ensuring that a record exists.