Issue Overview: Hidden Autoindexes and UNIQUE Constraint Implementation

SQLite implements UNIQUE constraints and PRIMARY KEY constraints through automatically generated indexes known as "sqlite_autoindex" objects. These indexes are not displayed in standard schema inspection commands like .schema TABLE, creating confusion about their existence, structure, and uniqueness properties. Developers working with SQLite often encounter discrepancies when comparing the declared table constraints with visible database artifacts, particularly when using third-party tools like SQLiteStudio that may misinterpret index metadata.

The core issue revolves around three key observations:

1. Invisibility in Schema Dumps: The .schema command deliberately omits autoindexes from its output
2. Metadata Interpretation Challenges: Database tools may incorrectly report index uniqueness for autoindexes
3. Structural Opaqueness: Autoindexes lack explicit documentation of their column composition and uniqueness in standard introspection methods

This behavior stems from SQLite’s design philosophy of maintaining backward compatibility while optimizing storage efficiency. The automatic indexes created for UNIQUE constraints differ from user-created indexes in both naming conventions and metadata handling, requiring specialized techniques for proper analysis.

Possible Causes: Autoindex Implementation Nuances and Tool Limitations

1. Autoindex Naming and Schema Filtering

SQLite generates unique indexes for UNIQUE/PK constraints using a specific naming pattern: sqlite_autoindex_TABLE_N where TABLE is the table name and N is a sequential number. The .schema command filters out objects with names matching the sqlite_autoindex% pattern to avoid cluttering schema dumps with implementation details. This intentional omission preserves the logical schema presentation while hiding physical implementation artifacts.

2. Index Uniqueness Misinterpretation

All SQLite indexes enforce uniqueness through their internal structure, but the mechanism varies:

• Declared UNIQUE indexes use column values as the key with rowid as payload
• Non-declared UNIQUE indexes use (column values + rowid) as composite key

Third-party tools like SQLiteStudio may misinterpret this implementation detail by:

1. Relying on the unique field from sqlite_schema table without considering autoindex special handling
2. Failing to account for SQLite’s rowid-based uniqueness enforcement in non-declared indexes
3. Misapplying SQL standard uniqueness semantics to SQLite’s specific implementation

3. Column Composition Opaqueness

Autoindexes do not expose their constituent columns through standard introspection methods. While user-created indexes explicitly store their column list in the sqlite_schema table, autoindexes derive their structure directly from the constraint definition. This creates challenges when trying to programmatically determine which columns participate in an autoindex.

Troubleshooting Steps: Autoindex Inspection and Validation

1. Comprehensive Index Enumeration

Execute this SQL query to reveal all indexes, including autoindexes:

SELECT 
  s.name AS index_name,
  s.sql AS index_sql,
  s.tbl_name AS table_name,
  ii.* 
FROM 
  sqlite_schema s
LEFT JOIN 
  pragma_index_info(s.name) ii 
WHERE 
  s.type = 'index'
ORDER BY 
  s.tbl_name, s.name;

Key columns to analyze:

• index_name: Autoindexes follow sqlite_autoindex_* pattern
• seqno: Column sequence in index (0-based)
• name: Column name in index
• collation: Collation sequence for column

2. Uniqueness Verification

Use combined metadata analysis to validate index uniqueness:

SELECT
  s.name AS index_name,
  s.tbl_name AS table_name,
  CASE 
    WHEN s.name LIKE 'sqlite_autoindex%' THEN 1 
    ELSE s."unique" 
  END AS is_unique,
  group_concat(ii.name, ', ') AS columns
FROM 
  sqlite_schema s
LEFT JOIN 
  pragma_index_info(s.name) ii 
WHERE 
  s.type = 'index'
GROUP BY 
  s.name
ORDER BY 
  s.tbl_name, s.name;

Interpretation guidelines:

1. All autoindexes (sqlite_autoindex%) should report is_unique = 1
2. User-created indexes show uniqueness from their original CREATE statement
3. Column composition validates that indexes match expected constraint columns

3. Advanced Pragma Analysis

Combine multiple pragmas for deep index inspection:

-- Table structure analysis
SELECT * FROM pragma_table_info('table_name');

-- Extended table structure including hidden columns
SELECT * FROM pragma_table_xinfo('table_name');

-- Index metadata
SELECT * FROM pragma_index_list('table_name');

-- Index column details
SELECT * FROM pragma_index_info('index_name');

-- Detailed index statistics
SELECT * FROM pragma_index_xinfo('index_name');

Critical observations from pragma output:

• pragma_index_list returns:
  • unique: 1 for UNIQUE indexes and autoindexes
  • origin: ‘c’ for autoindexes (constraint), ‘u’ for user-created
• pragma_index_xinfo shows:
  • key: 1 for index key columns
  • desc: Sort order (0=ASC, 1=DESC)
  • coll: Collation sequence name
  • keyflg: Key composition flags

4. Tool Validation Workflow

When suspecting tool misreporting (e.g., SQLiteStudio showing autoindexes as non-unique):

1. Raw SQLite Verification:

   SELECT 
     name, 
     "unique", 
     sql LIKE 'CREATE UNIQUE%' AS explicit_unique 
   FROM 
     sqlite_schema 
   WHERE 
     type = 'index';
   

2. Cross-Reference With Pragma:

   SELECT 
     il.name AS index_name,
     il."unique" AS pragma_unique,
     s."unique" AS schema_unique
   FROM 
     pragma_index_list('table_name') il
   JOIN 
     sqlite_schema s ON s.name = il.name;
   

3. Validate Using EXPLAIN:

   EXPLAIN QUERY PLAN
   SELECT * FROM table_name WHERE unique_column = ?;
   

   Check output for:

   DETAILS: USE TEMP B-TREE FOR UNIQUE INDEX (or similar)
   

5. Schema Reconstruction

To generate complete schema including autoindexes:

SELECT 
  type, name, tbl_name, sql 
FROM 
  sqlite_schema 
WHERE 
  name NOT LIKE 'sqlite_%'
UNION ALL
SELECT 
  type, 
  name, 
  tbl_name, 
  '-- AUTOINDEX: ' || substr(sql,1,instr(sql,'(')-1) AS sql 
FROM 
  sqlite_schema 
WHERE 
  name LIKE 'sqlite_autoindex%'
ORDER BY 
  type DESC, name;

This provides:

• Regular schema objects
• Autoindexes commented with their base definition
• Full catalog of all database artifacts

6. Migration and Compatibility Checks

When porting schemas between database systems:

1. Explicit Index Conversion:

   -- SQLite schema with hidden autoindex
   CREATE TABLE t1(a, b UNIQUE);
   
   -- Convert to explicit index for compatibility
   CREATE TABLE t1(a, b);
   CREATE UNIQUE INDEX t1_b_unique ON t1(b);
   

2. Constraint Verification:

   PRAGMA foreign_key_check;
   PRAGMA integrity_check;
   PRAGMA quick_check;
   

3. Index Collation Validation:

   SELECT 
     name, 
     "unique", 
     (SELECT group_concat(coll) 
      FROM pragma_index_xinfo(name) 
      WHERE keyflg IN (1,2)) AS collations
   FROM 
     sqlite_schema 
   WHERE 
     type = 'index';
   

7. Performance Optimization

When analyzing query plans involving autoindexes:

1. Force Index Usage:

   SELECT * FROM table_name INDEXED BY sqlite_autoindex_table_name_1 WHERE ...;
   

2. Compare With User Indexes:

   ANALYZE;
   EXPLAIN QUERY PLAN
   SELECT * FROM table_name WHERE unique_column = ?;
   
   CREATE INDEX manual_idx ON table_name(unique_column);
   EXPLAIN QUERY PLAN
   SELECT * FROM table_name WHERE unique_column = ?;
   DROP INDEX manual_idx;
   

3. Index Size Analysis:

   SELECT 
     name, 
     stat 
   FROM 
     sqlite_stat1 
   WHERE 
     tbl_name = 'table_name';
   

8. System Table Direct Inspection

For advanced debugging, query internal SQLite tables:

-- Raw index metadata
SELECT * FROM sqlite_master WHERE type = 'index';

-- Internal schema storage
SELECT * FROM sqlite_schema WHERE type = 'index';

-- Index page structure (requires debug build)
PRAGMA page_count;
PRAGMA page_size;
PRAGMA integrity_check;

9. Collation Sequence Handling

Verify index collation matches expectations:

SELECT 
  il.name AS index_name,
  ii.name AS column_name,
  ii.coll AS collation
FROM 
  pragma_index_list('table_name') il
JOIN 
  pragma_index_xinfo(il.name) ii 
WHERE 
  ii.keyflg IN (1,2);

Compare with table definition collations:

SELECT 
  name AS column_name,
  collation 
FROM 
  pragma_table_info('table_name');

10. Recovery and Repair Techniques

If autoindexes become corrupted:

1. Reconstruct Schema:

   .schema -- Save output
   .dump -- Full database dump
   

2. Export/Import:

   sqlite3 corrupted.db ".schema" > schema.sql
   sqlite3 new.db < schema.sql
   sqlite3 corrupted.db ".dump" | sqlite3 new.db
   

3. Vacuum Rebuild:

   VACUUM;
   

4. Index Rebuilding:

   REINDEX;
   

This comprehensive approach ensures complete visibility into SQLite’s automatic index management while providing tools to validate, optimize, and repair constraint implementations across different environments and toolchains.