Resolving “Database Main Already in Use” and Dynamic Table Reference Errors in SQLite

Issue Overview: Reserved Schema Names and Invalid Dynamic Table References

The core issues in the provided scenario revolve around two critical misunderstandings of SQLite’s schema architecture and query execution model:

1. Attempting to attach a database using the reserved schema name main, which is always in use by the primary database connection.
2. Invalid dynamic table references in a subquery that attempts to use a column value (name) as a table identifier without proper interpolation.

Reserved Schema Names

In SQLite, every database connection implicitly has two schemas: main (the primary database opened by the connection) and temp (the schema for temporary objects). These names are reserved and cannot be reassigned. Attempting to ATTACH a database using AS main will fail because main is already occupied. This explains the error:

[SQLITE_ERROR] SQL error or missing database (database main is already in use)

The user’s script begins with:

ATTACH DATABASE 'mstable_2022.sqlite' AS main;

This line triggers the error because main is reserved.

Invalid Dynamic Table References

The second part of the script uses a Common Table Expression (CTE) to fetch table names from sqlite_master and then attempts to check if those tables are empty:

SELECT name
FROM sqlite_master
WHERE NOT EXISTS (
  SELECT 1
  FROM main.[name]
  LIMIT 1
)

Here, main.[name] is interpreted literally as a table named name in the main schema. Since no such table exists, this results in:

[SQLITE_ERROR] SQL error or missing database (no such table: main.name)

The user mistakenly assumes that [name] dynamically references the name column from the CTE, but SQLite does not support this kind of variable interpolation in table names.

Additional Syntax Errors

The CTE is declared but not used in a subsequent query. The WITH clause is valid only when followed by a SELECT, INSERT, UPDATE, or DELETE statement. The user’s code omits this, causing syntax errors.

Misuse of DETACH

The script ends with:

DETACH DATABASE 'mstable_2022.sqlite';

DETACH requires the schema name (alias) assigned during ATTACH, not the original filename. Since the ATTACH failed (due to using main), the DETACH command references an invalid schema.

Possible Causes: Schema Aliasing Conflicts and Static Query Parsing

1. Reserved Schema Name Assignment

• Root Cause: The ATTACH command assigns the alias main to the database mstable_2022.sqlite, but main is reserved for the primary database.
• Why It Matters: SQLite reserves main and temp for internal use. Any attempt to attach a database with these names will fail.

2. Static Parsing of Table Names

• Root Cause: The subquery FROM main.[name] uses the column name from the CTE as a table identifier. SQLite parses table names statically at query compilation, not dynamically at runtime.
• Why It Matters: The query engine cannot infer that [name] refers to a variable; it treats it as a literal table name.

3. CTE Without a Follow-Up Query

• Root Cause: The WITH all_tables AS (...) block is not followed by a SELECT, INSERT, or other DML statement.
• Why It Matters: CTEs are temporary result sets used within a larger query. Declaring a CTE without using it is syntactically invalid.

4. Incorrect DETACH Syntax

• Root Cause: The DETACH command references the filename instead of the schema alias.
• Why It Matters: DETACH operates on schema names, not filenames.

Troubleshooting Steps, Solutions & Fixes

Step 1: Correct Schema Aliasing

Problem: The ATTACH command uses the reserved name main.
Solution: Use a non-reserved alias (e.g., secondary):

ATTACH DATABASE 'mstable_2022.sqlite' AS secondary;

Explanation:

• secondary is a valid, user-defined schema name.
• The primary database remains accessible as main.

Revised Script:

ATTACH DATABASE 'mstable_2022.sqlite' AS secondary;
-- Rest of the script...
DETACH DATABASE secondary;  -- Use schema name, not filename

Step 2: Fix Dynamic Table References

Problem: The subquery FROM main.[name] uses name as a variable.
Solution: Use dynamic SQL or iterative table checks.

Option 1: Use the eval Extension (SQLite 3.35.0+)

The eval extension allows dynamic SQL execution. Enable it using SELECT load_extension('eval'); (if available).

WITH all_tables AS (
  SELECT name
  FROM secondary.sqlite_master  -- Use the attached schema
  WHERE type = 'table'
)
SELECT name
FROM all_tables
WHERE eval(
  'SELECT NOT EXISTS (SELECT 1 FROM secondary.' || quote_ident(name) || ' LIMIT 1)'
);

Explanation:

• quote_ident(name) safely escapes table names.
• eval() executes the generated query string.

Option 2: Scripting Language Integration

If using a scripting language (e.g., Python), fetch table names first, then check each table:

import sqlite3

conn = sqlite3.connect('primary.db')
conn.execute("ATTACH DATABASE 'mstable_2022.sqlite' AS secondary")

tables = conn.execute("SELECT name FROM secondary.sqlite_master WHERE type='table'").fetchall()

empty_tables = []
for table in tables:
    table_name = table[0]
    has_rows = conn.execute(f"SELECT EXISTS (SELECT 1 FROM secondary.{table_name} LIMIT 1)").fetchone()[0]
    if not has_rows:
        empty_tables.append(table_name)

print(empty_tables)
conn.execute("DETACH DATABASE secondary")

Step 3: Fix CTE Syntax

Problem: The CTE is declared but not used.
Solution: Add a SELECT statement after the CTE.

ATTACH DATABASE 'mstable_2022.sqlite' AS secondary;

WITH all_tables AS (
  SELECT name
  FROM secondary.sqlite_master
  WHERE type = 'table'
)
SELECT name
FROM all_tables
WHERE NOT EXISTS (
  SELECT 1
  FROM secondary.books  -- Static example; dynamic checks require eval/scripting
  LIMIT 1
);

DETACH DATABASE secondary;

Step 4: Validate DETACH Syntax

Problem: DETACH references the filename instead of the schema alias.
Solution: Use the schema name:

DETACH DATABASE secondary;  -- Correct

Step 5: Debugging Line-Specific Errors

Problem: SQLite does not report line numbers in errors.
Solution: Use incremental execution:

1. Execute the ATTACH statement alone.
2. Run the CTE and SELECT separately.
3. Validate each subquery.

Final Corrected Script

ATTACH DATABASE 'mstable_2022.sqlite' AS secondary;

WITH all_tables AS (
  SELECT name
  FROM secondary.sqlite_master
  WHERE type = 'table'
)
SELECT name
FROM all_tables
WHERE eval(
  'SELECT NOT EXISTS (SELECT 1 FROM secondary.' || quote_ident(name) || ' LIMIT 1)'
);

DETACH DATABASE secondary;

Summary of Key Fixes

1. Avoid Reserved Schema Names: Use secondary instead of main for attached databases.
2. Dynamic Table Checks: Use eval or scripting to interpolate table names.
3. CTE Syntax: Ensure CTEs are followed by a query.
4. Correct DETACH Syntax: Reference schema aliases, not filenames.

By addressing these areas, the script will correctly identify empty tables without triggering schema conflicts or parsing errors.