Resolving SQLITE_CONSTRAINT_PRIMARYKEY Errors in FTS5 Virtual Tables with INSERT OR IGNORE

Understanding FTS5 Virtual Table Behavior During rowid-Explicit Inserts

The SQLITE_CONSTRAINT_PRIMARYKEY error (SQLITE_ERROR code 1555) occurring during INSERT OR IGNORE operations on an FTS5 virtual table is rooted in the interaction between explicit rowid assignments, FTS5’s internal constraint enforcement mechanisms, and historical limitations of virtual table implementations. This error manifests when an application explicitly provides a rowid value that conflicts with an existing entry in the FTS5 table’s underlying content storage. While the OR IGNORE clause typically suppresses primary key violations in standard SQLite tables, FTS5 virtual tables historically lacked native support for handling such conflicts at the virtual table interface layer. This discrepancy arises from architectural differences between conventional tables and virtual tables like FTS5, which manage their data storage through abstracted backend structures.

Primary Contributors to FTS5 Constraint Violations Despite OR IGNORE

1. FTS5’s Shadow Table Architecture and rowid Management

FTS5 virtual tables rely on a hidden content table (e.g., search_urls_content) that stores the rowid and column values. When an explicit rowid is provided during insertion, FTS5 attempts to write this value directly into the content table. If the rowid already exists in the content table, SQLite’s core engine throws a primary key constraint violation. Crucially, the OR IGNORE clause operates at the SQL parser level, not at the virtual table implementation level. FTS5’s virtual table module historically did not propagate conflict resolution directives (like IGNORE) to its underlying content table, bypassing the expected suppression of duplicate rowid errors.

2. Absence of UPSERT Support in Virtual Tables

SQLite’s UPSERT syntax (ON CONFLICT clauses) is unsupported for virtual tables due to limitations in the virtual table API. When an INSERT OR IGNORE statement is executed, the SQL parser translates it into an INSERT with an ON CONFLICT DO NOTHING clause. However, virtual tables like FTS5 do not implement the xUpdate method required to handle conflict resolutions, resulting in the parser error: "UPSERT not implemented for virtual table". This forces developers to rely on application-layer checks for rowid existence before insertion—a process prone to race conditions in concurrent environments.

3. Wasm Build-Specific Query Compilation Nuances

The SQLite Wasm build (version 3.43 in this case) may exhibit subtle behavioral differences compared to native builds due to its JavaScript-bound API and sandboxed execution environment. While the core SQLite engine remains consistent, the Wasm version’s handling of virtual table transactions and error reporting could amplify edge cases in FTS5 constraint checks, particularly when explicit rowid assignments coincide with high-frequency insertions from browser extensions.

Comprehensive Mitigation Strategies and Permanent Fixes

1. Upgrading to SQLite Versions with FTS5 Constraint Handling Patches

A fix for this specific FTS5 constraint issue was committed to the SQLite source tree on 2023-09-16 (post-version 3.43). Applications should integrate the latest SQLite Wasm build containing this patch. For projects locked to version 3.43, backporting the fix involves modifying the fts5_config.c module to ensure the virtual table’s xUpdate method respects the ON CONFLICT directive during explicit rowid inserts. This requires recompiling the Wasm binary with the patched source.

2. Redesigning Insertion Logic to Avoid Explicit rowid Assignments

FTS5 automatically generates unique rowid values when they are omitted during insertion. Rewriting the insert statement to exclude rowid eliminates the risk of primary key collisions:

INSERT OR IGNORE INTO search_urls (url, title) VALUES (?, ?)

If the application logic requires tracking specific rowid values (e.g., for cross-table references), implement a separate mapping table that correlates external identifiers with FTS5’s auto-generated rowids.

3. Implementing Application-Layer rowid Existence Checks

Before inserting with an explicit rowid, perform a preliminary lookup:

SELECT rowid FROM search_urls WHERE rowid = ? LIMIT 1

If the query returns a result, skip the insertion. While this approach introduces a round-trip overhead, it circumvents the constraint violation. Use transactional blocks (BEGIN IMMEDIATE/COMMIT) to prevent race conditions between the SELECT and INSERT operations.

4. Utilizing FTS5’s External Content Tables for rowid Synchronization

Configure the FTS5 table to reference an external content table that enforces rowid uniqueness independently:

CREATE TABLE search_urls_content(id INTEGER PRIMARY KEY, url TEXT, title TEXT);
CREATE VIRTUAL TABLE search_urls USING fts5(url, title, content='search_urls_content', content_rowid='id');

This delegates rowid management to the search_urls_content table, which fully supports INSERT OR IGNORE semantics. Inserts into search_urls will first check the content table for id conflicts, leveraging SQLite’s native constraint resolution.

5. Adopting Delete-Insert Workflows for rowid Reuse Scenarios

In cases where specific rowid values must be preserved (e.g., data restoration), explicitly delete the existing row before reinserting:

DELETE FROM search_urls WHERE rowid = ?;
INSERT INTO search_urls (rowid, url, title) VALUES (?, ?, ?);

Wrap these operations in a transaction to ensure atomicity. Note that this method temporarily removes the row from search results until the insertion completes.

6. Monitoring SQLite Wasm Binding Interactions

The Chrome extension environment may impose restrictions on synchronous SQLite API calls, leading to unanticipated interleaving of insert operations. Ensure all database accesses occur through asynchronous Web Workers, and validate that the Wasm build’s JavaScript glue code correctly propagates error codes from virtual table operations. Instrument the extension to log all SQL errors with stack traces, focusing on cases where multiple tabs or windows concurrently modify the FTS5 table.

7. Fallback to FTS4 for Legacy Compatibility

As a last resort, consider downgrading to FTS4, which lacks some FTS5 features but implements INSERT OR IGNORE reliably in scenarios with explicit rowid assignments. Recreate the virtual table:

CREATE VIRTUAL TABLE IF NOT EXISTS search_urls USING fts4(url, title, tokenize=unicode61 "remove_diacritics=2");

Test thoroughly, as FTS4 has differing tokenization rules and storage efficiency trade-offs.

By addressing the FTS5 virtual table’s constraint enforcement mechanics, aligning insertion strategies with SQLite’s conflict resolution capabilities, and accounting for Wasm-specific runtime behaviors, developers can eliminate SQLITE_CONSTRAINT_PRIMARYKEY errors while maintaining robust full-text search functionality in browser extensions and other Wasm-hosted applications.