SQLite: Copy rows without losing table constraints
A scratch copy made with CREATE TABLE AS SELECT can accept rows the original table rejects. Define the destination schema explicitly when the copied table must enforce the same rules.
Set up and run
Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and the standard mktemp, rm and rmdir utilities. No additional packages or SQLite CLI are needed. Keep the same terminal open through cleanup. Stop if setup fails before saving the source.
start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1Save as copy-table.py:
import sqlite3
db = sqlite3.connect(":memory:", isolation_level=None)
try:
db.executescript("""
CREATE TABLE source (
code TEXT PRIMARY KEY NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0)
);
INSERT INTO source VALUES ('pen', 4);
CREATE TABLE loose AS SELECT code, quantity FROM source;
CREATE TABLE checked (
code TEXT PRIMARY KEY NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0)
);
INSERT INTO checked SELECT code, quantity FROM source;
""")
db.execute("INSERT INTO loose VALUES ('pen', -2)")
db.execute("INSERT INTO loose (code) VALUES ('book')")
print("CTAS rows:", db.execute(
"SELECT code, quantity FROM loose ORDER BY rowid").fetchall())
for label, sql in [
("duplicate", "INSERT INTO checked VALUES ('pen', 2)"),
("negative", "INSERT INTO checked VALUES ('eraser', -2)"),
("null", "INSERT INTO checked VALUES ('ruler', NULL)"),
]:
try:
db.execute(sql)
except sqlite3.IntegrityError:
print(label + ": rejected")
else:
raise AssertionError(label + " unexpectedly accepted")
db.execute("INSERT INTO checked (code) VALUES ('book')")
print("explicit schema:", db.execute(
"SELECT code, quantity FROM checked ORDER BY code").fetchall())
finally:
db.close()Run:
python3 copy-table.pyObserved output:
CTAS rows: [('pen', 4), ('pen', -2), ('book', None)]
duplicate: rejected
negative: rejected
null: rejected
explicit schema: [('book', 0), ('pen', 4)]Inspect what the copy accepts
The original row describes four pens. The CTAS copy accepts a second pen row with a negative quantity, despite the source table’s primary key and check constraint. It also stores NULL for the book’s omitted quantity instead of applying the source default of zero.
The explicitly declared destination rejects each invalid insert. Its book row receives the declared default. Each rejected statement uses the normal ABORT behavior; no IGNORE policy hides the error. The script catches only the expected integrity exception and fails if invalid data is accepted.
SQLite’s CREATE TABLE AS documentation states that this form creates no primary key or constraints and gives columns a NULL default. The selected expressions’ affinities determine the destination column types; copying a table’s rows does not reproduce its declaration.
Use a schema definition for a durable copy
Keep the intended CREATE TABLE statement in a migration or other reviewed schema source, then copy named columns with INSERT INTO … SELECT. For a real migration, inventory indexes, triggers, foreign keys and other schema objects separately. This example covers one table’s column constraints and default, not a complete database clone.
CTAS is useful for temporary analysis where an unconstrained result table is intentional. Before putting that table on an application write path, test duplicate keys, missing values and invalid quantities. Existing data that violates a newly declared constraint should fail visibly rather than be silently discarded.
All example tables are in memory. No persistent database is opened or altered, and this example does not test concurrent migration or crash recovery.
Clean up
In the same terminal, remove only the example file and the empty scratch directory. If the directory is not empty, inspect what remains before deleting anything else.
rm -- copy-table.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dirAcknowledgements
This post was created with assistance from AI.