Schema Replication and Primary Key Handling in SQLite CREATE TABLE AS SELECT Queries

Schema Replication Pitfalls When Using CREATE TABLE AS SELECT With INTEGER PRIMARY KEY

Issue Overview: Schema Structure Omission and Primary Key Conflicts

The core challenge arises when attempting to replicate a table structure and specific data rows between SQLite databases using CREATE TABLE AS SELECT (CTAS). While this syntax provides a quick way to create new tables populated with query results, it introduces two critical limitations:

1. Schema Element Omission:
   CTAS does not preserve schema definitions beyond basic column names and data type affinity. This includes:

   • PRIMARY KEY constraints
   • UNIQUE constraints
   • CHECK constraints
   • DEFAULT values
   • FOREIGN KEY relationships
   • Indexes
   • Collation sequences

2. Rowid/INTEGER PRIMARY KEY Behavior:
   When a column is declared as INTEGER PRIMARY KEY, it becomes an alias for SQLite’s internal rowid column. CTAS creates a new table with its own rowid sequence, causing unexpected behavior when:

   • Copying specific rows between databases
   • Attempting to maintain rowid continuity
   • Handling primary key conflicts during cross-database inserts

In the described workflow:

ATTACH DATABASE '/path/to/other/database' AS DST;
CREATE TABLE DST.MYTAB AS SELECT * FROM MAIN.MYTAB WHERE ABSID=1234;

The WHERE clause filters source data but does not address the fundamental schema mismatch. The destination table DST.MYTAB will:

• Lose its INTEGER PRIMARY KEY designation
• Generate a new implicit rowid column
• Fail to maintain any constraints from the source table

This leads to subsequent issues when inserting data into properly structured destination tables, as demonstrated in the attempted workflow:

INSERT INTO DST.MYTAB SELECT * FROM MEM.MYTAB;

Without proper schema replication, this risks:

• Primary key collisions
• Constraint violations
• Data type mismatches
• Loss of referential integrity

Root Causes: Why Schema and Key Management Fail

Three primary factors contribute to the observed issues:

1. CTAS Schema Simplification
SQLite’s CREATE TABLE AS SELECT implementation creates tables using only the result set of the query, not the original table’s schema definition. The new table:

• Inherits column names from the query result
• Assigns type affinity based on stored values, not original declarations
• Omits all constraints and indexes
• Creates a new implicit rowid column unless explicitly defined

Example:
Original table:

CREATE TABLE MAIN.MYTAB (
    ABSID INTEGER PRIMARY KEY,
    DATA TEXT NOT NULL CHECK(LENGTH(DATA) > 0)
);

CTAS-generated table:

-- Implicit structure after CREATE TABLE AS SELECT
CREATE TABLE DST.MYTAB (
    ABSID INTEGER,
    DATA TEXT
);

2. Rowid Aliasing Complexity
When using INTEGER PRIMARY KEY:

• The column becomes an alias for rowid
• rowid values are automatically assigned if not explicitly provided
• Duplicate values are prohibited
• Values can be manually set (unlike auto-increment in other DBMS)

This creates conflict scenarios when:

• Copying rows between tables with existing data
• Attempting to preserve original row identifiers
• Using temporary tables as transfer intermediaries

3. SELECT * Column Propagation
Using SELECT * in CTAS introduces maintenance risks:

• Column order dependency
• Hidden column exposure (e.g., rowid aliases)
• Schema change fragility
• Type affinity determination from current data rather than original definitions

Comprehensive Solutions: Schema Preservation and Safe Data Transfer

1. Full Schema Replication Protocol
Step 1: Clone Schema Structure
Use SELECT sql FROM sqlite_master to retrieve original table definitions:

-- Retrieve source table schema
SELECT sql FROM MAIN.sqlite_master
WHERE type='table' AND name='MYTAB';

-- Example output:
CREATE TABLE MYTAB (
    ABSID INTEGER PRIMARY KEY,
    DATA TEXT NOT NULL CHECK(LENGTH(DATA) > 0)
)

-- Execute in destination database
ATTACH DATABASE '/path/to/other/database' AS DST;
EXECUTE IMMEDIATE 'CREATE TABLE DST.MYTAB (
    ABSID INTEGER PRIMARY KEY,
    DATA TEXT NOT NULL CHECK(LENGTH(DATA) > 0)
)';

Step 2: Recreate Indexes and Triggers
Repeat the process for supporting objects:

-- Retrieve indexes
SELECT sql FROM MAIN.sqlite_master
WHERE type='index' AND tbl_name='MYTAB';

-- Retrieve triggers
SELECT sql FROM MAIN.sqlite_master
WHERE type='trigger' AND tbl_name='MYTAB';

2. Rowid Management Techniques
Method A: Explicit Column Exclusion
Create a view that nullifies the INTEGER PRIMARY KEY:

-- In source database
CREATE VIEW MAIN.MYTAB_NULL_ROWID AS
SELECT NULL AS ABSID, DATA FROM MAIN.MYTAB;

-- Insert into destination
INSERT INTO DST.MYTAB(DATA)
SELECT DATA FROM MAIN.MYTAB_NULL_ROWID WHERE ABSID=1234;

Method B: Column List Specification
Explicitly exclude the primary key in inserts:

INSERT INTO DST.MYTAB(DATA)
SELECT DATA FROM MAIN.MYTAB WHERE ABSID=1234;

3. Data Migration Workflow
Safe Row Transfer Procedure

-- Attach databases
ATTACH DATABASE '/path/to/other/database' AS DST;

-- Clone schema (as shown above)
-- ...

-- Insert data with rowid management
INSERT INTO DST.MYTAB(DATA)
SELECT DATA FROM MAIN.MYTAB
WHERE ABSID=1234;

-- Verify new rowid assignment
SELECT last_insert_rowid() FROM DST.MYTAB;

4. Transactional Integrity
Wrap operations in transactions to ensure atomicity:

BEGIN TRANSACTION;

ATTACH DATABASE '/path/to/other/database' AS DST;

-- Schema cloning
-- ...

-- Data transfer
INSERT INTO DST.MYTAB(...)
SELECT ... FROM MAIN.MYTAB
WHERE ...;

COMMIT;

5. Schema Validation Checks
Verify table structures before data transfer:

-- Compare column definitions
PRAGMA MAIN.table_info(MYTAB);
PRAGMA DST.table_info(MYTAB);

-- Check primary key existence
SELECT name FROM MAIN.pragma_table_info('MYTAB') WHERE pk > 0;
SELECT name FROM DST.pragma_table_info('MYTAB') WHERE pk > 0;

6. Advanced Technique: Backup API Integration
For large-scale transfers, use SQLite’s backup API:

// C API example
sqlite3_backup_init(dest_db, "main", source_db, "main");
sqlite3_backup_step(backup, -1);
sqlite3_backup_finish(backup);

This preserves all schema elements and data while handling rowid sequences correctly.

By implementing these solutions, developers ensure proper schema replication, maintain primary key integrity, and enable safe data transfers between SQLite databases. The key insight is separating schema definition from data population – a critical distinction often overlooked when using CTAS for table creation.