Integrity Check Error: TEXT Value in INTEGER Column Despite Numeric Conversion

Understanding the TEXT Value in INTEGER Column Integrity Failure

Issue Overview
The core issue arises when SQLite’s PRAGMA integrity_check reports errors such as 'TEXT VALUE IN MD_TAG_DATA.OID' for a column explicitly defined with INTEGER affinity. This occurs even though SQLite typically converts text values that represent integers into the INTEGER storage class automatically. The problem manifests in non-STRICT tables where the database engine detects text values that should have been converted to integers but were not, violating internal consistency checks.

In the provided scenario, the md_tag_data.oid column is declared as INTEGER, and the application uses sqlite3_bind_int64() to write values, which should enforce integer storage. However, 279 rows in a 14-million-row table were found to have oid values stored as TEXT instead of INTEGER, all containing the numeric string '114862'. This inconsistency triggers the integrity check failure.

Key observations:

1. The column is not declared as INTEGER PRIMARY KEY, so it lacks the stricter enforcement of rowid-like behavior.
2. The offending values are valid integers stored as text, which SQLite should have converted during insertion.
3. The issue is isolated to a single user’s database, suggesting a localized anomaly rather than systemic application flaws.

Root Causes of TEXT in INTEGER Columns
The presence of text values in an INTEGER column in a non-STRICT table can be attributed to three primary factors:

1. Direct Insertion of Text Values Bypassing Conversion

   • SQLite converts text values to INTEGER or REAL when inserted into NUMERIC/INTEGER affinity columns only if the text is a well-formed integer or real literal. However, this conversion happens at the point of insertion. If the insertion mechanism explicitly forces a text type (e.g., using sqlite3_bind_text() with '114862'), SQLite stores the value as TEXT without conversion. This bypasses the automatic type coercion, leading to a mismatch between the expected and actual storage classes.

2. Database Corruption or Edge-Case Bugs

   • While rare, database corruption or SQLite engine bugs could theoretically cause values to be misclassified. However, PRAGMA integrity_check did not report other errors, making this less likely.

3. External Modifications to the Database

   • Third-party tools, manual edits, or scripts that directly modify the database (e.g., using sqlite3.exe or libraries that bind values as text) can introduce text values into INTEGER columns. This is particularly plausible here, as only one user encountered the issue, implying a unique workflow or tooling.

Resolution: Diagnosing, Fixing, and Preventing the Issue

Step 1: Identify and Validate Offending Rows
Run a diagnostic query to confirm the presence of text values and their exact content:

SELECT oid, typeof(oid), rowid 
FROM md_tag_data 
WHERE typeof(oid) <> 'integer';  

This returns the oid value (displayed without quotes even if stored as text), its storage class, and the rowid for further analysis.

Step 2: Repair the Column
Convert text values to INTEGER using an UPDATE statement:

UPDATE md_tag_data  
SET oid = CAST(oid AS INTEGER)  
WHERE typeof(oid) <> 'integer';  

After executing this, re-run PRAGMA integrity_check to ensure no further errors.

Step 3: Investigate the Source of Text Values
To prevent recurrence:

• Audit application code for all database operations involving md_tag_data.oid. Ensure sqlite3_bind_int64() is used consistently.
• Check for raw SQL statements (e.g., INSERT INTO md_tag_data (oid) VALUES ('114862')) that might bind oid as text.
• Verify that third-party tools or user scripts interacting with the database adhere to type constraints.

Step 4: Strengthen Schema Enforcement
Consider schema modifications to reduce ambiguity:

• Add a CHECK constraint:

  ALTER TABLE md_tag_data  
  ADD CHECK (typeof(oid) = 'integer');  
  

  This explicitly rejects non-integer values but requires careful testing.

• Use STRICT tables (SQLite 3.37+):

  CREATE TABLE md_tag_data (  
    oid INTEGER,  
    ...  
  ) STRICT;  
  

  STRICT tables enforce column types, preventing invalid data insertion.

Step 5: Monitor and Log Database Operations
Implement logging for database writes to detect unexpected text bindings. For example, use SQLite’s sqlite3_trace() or application-level logging to record bound parameter types during INSERT/UPDATE operations.

Conclusion
The error stems from text values being stored in an INTEGER column due to atypical insertion methods. The fix involves converting existing text values to integers and tightening data validation. By auditing code, enforcing stricter schemas, and monitoring database interactions, this issue can be resolved and prevented in the future.