Why SQLite CREATE TABLE AS SELECT drops the primary key

SQLite’s CREATE TABLE ... AS SELECT copies query results into a new table without the source constraints. The IDs can look right after the copy even though the primary key is gone. A second row with the same ID can make an update intended for one task change both. SQLite reference for CREATE TABLE AS SELECT

The row count passes and the next write breaks the assumption

Open an in-memory database with sqlite3 :memory: and run this SQL in that session. The task app uses id to identify one row. The original table enforces that rule with PRIMARY KEY.

CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
INSERT INTO tasks (id, title) VALUES (7, 'Check inventory');

CREATE TABLE tasks_copy AS SELECT id, title FROM tasks;

SELECT (SELECT count(*) FROM tasks) AS source_count,
       (SELECT count(*) FROM tasks_copy) AS copy_count;

Both counts are 1. The next insert gives a different task the same ID, then an update targets that ID.

INSERT INTO tasks_copy (id, title) VALUES (7, 'Order supplies');

UPDATE tasks_copy SET title = 'Count stock' WHERE id = 7;
SELECT changes() AS rows_updated;

SELECT id, title FROM tasks_copy;

On SQLite 3.51.0, rows_updated is 2. Both rows now contain 7|Count stock in the CLI’s default output format. The WHERE clause selected every row with that ID. The old primary key was what made that condition match at most one task. SQLite reference for the changes() function

The definitions in sqlite_schema show what changed before any new rows are written.

SELECT sql FROM sqlite_schema WHERE name IN ('tasks', 'tasks_copy');

The source definition contains id INTEGER PRIMARY KEY. The copy contains id INT with no key declaration. SQLite derives the new columns from the query’s output. Source constraints such as PRIMARY KEY and NOT NULL are absent from the resulting definition. SQLite schema table reference, SQLite reference for CREATE TABLE AS SELECT

Copy rows into a table with a declared key

CREATE TABLE ... AS SELECT works for a scratch table that only needs to hold query results. For a live table, declare the constraints the app depends on, then copy the rows with INSERT ... SELECT.

CREATE TABLE tasks_next (id INTEGER PRIMARY KEY, title TEXT NOT NULL);

INSERT INTO tasks_next (id, title)
SELECT id, title FROM tasks;

INSERT INTO tasks_next (id, title) VALUES (7, 'Order supplies');

The final insert fails with UNIQUE constraint failed: tasks_next.id. The same insert succeeds with ID 8. SQLite INSERT reference

A migration test can compare row counts and attempt a duplicate insert in the destination. The first check confirms the rows arrived. The second confirms the key is enforced.