UPSERT Fails on Composite UNIQUE Constraint Unless WITHOUT ROWID

Understanding the UPSERT Parse Error with Composite UNIQUE Constraints

Issue Overview
The core problem arises when attempting an INSERT ... ON CONFLICT (UPSERT) operation targeting a composite UNIQUE constraint in SQLite. The operation fails with a parse error:
Parse error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

This error occurs despite the table explicitly defining a composite UNIQUE constraint that matches the columns specified in the ON CONFLICT clause. The anomaly disappears when the table is declared as WITHOUT ROWID, even though the schema remains otherwise identical.

Example Schema and Query
The following table definitions and UPSERT query demonstrate the issue:

-- Table definitions
CREATE TABLE a(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
CREATE TABLE b(
  id INTEGER PRIMARY KEY REFERENCES a ON DELETE CASCADE,
  hash TEXT UNIQUE,
  timeout INTEGER,
  UNIQUE (id, hash)  -- Composite UNIQUE constraint
);
CREATE TABLE c(hash TEXT, timeout INTEGER);

-- Problematic UPSERT
INSERT INTO b (id, hash, timeout)
SELECT $id, hash, timeout FROM c WHERE true
ON CONFLICT (id, hash) DO NOTHING;  -- Fails with parse error

When b is redefined as WITHOUT ROWID, the same UPSERT succeeds:

CREATE TABLE b(...) WITHOUT ROWID;  -- No other changes
-- UPSERT now works without error

Key Observations

1. Schema Validity: The composite UNIQUE (id, hash) constraint is valid and enforced in both WITH ROWID and WITHOUT ROWID tables.
2. Error Context: The parse error occurs during query compilation, indicating that SQLite’s parser does not recognize the composite constraint as a valid conflict target in WITH ROWID tables.
3. ROWID Impact: The presence or absence of the implicit ROWID column alters how SQLite internally manages constraints, affecting UPSERT behavior.

Root Causes of the UPSERT Parse Error

1. Implicit ROWID and Constraint Indexing
In WITH ROWID tables, the ROWID (or its alias, id in this case) introduces hidden behavior:

• The PRIMARY KEY is implemented as a UNIQUE index over the ROWID.
• Composite UNIQUE constraints generate separate indexes, but their visibility to the UPSERT parser depends on internal SQLite logic.

In WITHOUT ROWID tables, the PRIMARY KEY is stored as the table’s clustered index, and composite UNIQUE constraints are treated as first-class indexes. This structural difference resolves the parser’s ambiguity.

2. Parser Limitations in Constraint Matching
SQLite’s UPSERT implementation requires the ON CONFLICT clause to reference exact column lists of a PRIMARY KEY or UNIQUE constraint. However, the parser may fail to associate the conflict target with a composite constraint in WITH ROWID tables due to:

• Overlap with the ROWID alias (id in this case), causing misidentification of the conflict target.
• Internal prioritization of single-column constraints (e.g., hash TEXT UNIQUE) over composite constraints during parsing.

3. SQLite Version-Specific Behavior
The error was observed in SQLite 3.41.1. Subsequent releases may address this inconsistency, but version-specific parser logic or indexing optimizations could contribute to the issue.

Resolving the UPSERT Composite Constraint Error

Step 1: Verify Constraint and Index Availability
Confirm that the composite UNIQUE constraint is properly indexed using:

PRAGMA index_list(b);  -- Check for an index on (id, hash)

If the index is missing, recreate the table or explicitly create the index:

CREATE INDEX idx_b_id_hash ON b(id, hash);

Step 2: Simplify the Schema
Isolate the issue by removing unrelated constraints and dependencies:

1. Drop the hash TEXT UNIQUE constraint.
2. Temporarily disable foreign keys (PRAGMA foreign_keys=OFF).
3. Retest the UPSERT to determine if secondary constraints interfere.

Step 3: Use Explicit Index Names (Workaround)
While SQLite does not support referencing indexes by name in ON CONFLICT, explicitly naming the composite constraint can improve clarity:

CREATE TABLE b(
  ...
  CONSTRAINT u_id_hash UNIQUE (id, hash)
);

Step 4: Adopt WITHOUT ROWID Where Appropriate
If the table does not require ROWID functionality, redefine it as WITHOUT ROWID:

CREATE TABLE b(...) WITHOUT ROWID;  -- Resolves parser ambiguity

Trade-offs:

• WITHOUT ROWID tables lack AUTOINCREMENT support and have stricter PRIMARY KEY requirements.
• Storage efficiency improves for tables with large or frequent composite key lookups.

Step 5: Upgrade SQLite
Test the UPSERT in SQLite 3.41.2 or newer. Check the changelog for fixes related to:

• Constraint parsing in INSERT ... ON CONFLICT.
• Index visibility in WITH ROWID tables.

Step 6: Report the Issue
If the problem persists across versions, submit a detailed report to SQLite’s issue tracker, including:

• Minimal reproducible schema.
• Observed vs. expected behavior.
• SQLite version and platform details.

Permanent Fix
For immediate resolution, use WITHOUT ROWID or redefine constraints to avoid composite targets in WITH ROWID tables. Monitor SQLite updates for parser improvements.

Final Notes
The interaction between ROWID, constraint indexing, and UPSERT parsing reveals subtle complexities in SQLite’s architecture. By aligning table design with SQLite’s internal mechanics—such as preferring WITHOUT ROWID for composite key-centric schemas—developers can avoid parser ambiguities and ensure reliable UPSERT operations.