Nami Smart LLC

D1 cannot rebuild a table that is referenced

Updated: September 6, 2026 CloudflareD1SQLiteDatabase design

On Cloudflare D1 you cannot change a CHECK constraint or a column definition on a table that is referenced by a foreign key. Adding one value to an enum is enough to hit this. Here is why, and what to do when the change has to happen anyway.

What cannot be done

Take this schema and try to add 'premium' to the CHECK on customers.tier.

CREATE TABLE customers (
  id   TEXT PRIMARY KEY,
  tier TEXT NOT NULL DEFAULT 'free'
       CONSTRAINT customers_tier_enum CHECK (tier IN ('free', 'standard'))
);

CREATE TABLE orders (
  id          TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
  id       INTEGER PRIMARY KEY,
  order_id TEXT NOT NULL REFERENCES orders(id),
  sku      TEXT NOT NULL,
  quantity INTEGER NOT NULL
);

A CHECK constraint cannot be changed with ALTER TABLE

SQLite stores a table’s definition in sqlite_schema as the text of its CREATE TABLE statement, and a CHECK constraint is part of that text. ALTER TABLE offers only ADD COLUMN, RENAME COLUMN, RENAME TO and a restricted DROP COLUMN; there is no syntax that edits the CHECK inside the stored definition. The definition has to be replaced instead, which SQLite documents as these four statements.

CREATE TABLE customers_new (...definition with the new CHECK...);
INSERT INTO customers_new SELECT * FROM customers;
DROP TABLE customers;                      -- this is the problem
ALTER TABLE customers_new RENAME TO customers;

To orders, which references customers, the third statement is the parent disappearing. Even though a table of the same name comes back in the fourth, at the third the foreign key points at nothing. What happens in that moment is where D1 differs from other SQLite environments.

D1 has no way to turn foreign keys off

SQLite’s own procedure wraps the four statements in PRAGMA foreign_keys = OFF. With foreign keys disabled, the third statement does nothing to orders.

Sending that statement to D1 does not disable them. SQLite ignores PRAGMA foreign_keys inside a transaction, and D1 runs every statement in an implicit transaction, so there is no way to run it outside one. The statement does not error, and the setting does not change.

PRAGMA defer_foreign_keys, which D1 does accept, is not a substitute. It only delays foreign key checks until commit, and Cloudflare’s documentation states that it does not stop ON DELETE CASCADE from firing. So the third statement ends one of two ways, depending on the child:

  • with ON DELETE CASCADE, the rows in orders are deleted
  • with NO ACTION, the commit fails

There is therefore no safe way to replace a referenced table on D1.

Making the change anyway

It is possible. Drop the foreign key constraints on the referencing side, recreate the parent table, then recreate the constraints. For three tables (order_items → orders → customers) that is three migrations.

#Contents
0001Recreate order_items, orders, customers in that order, dropping the foreign key constraints on the way
0002Recreate the orders → customers foreign key constraint
0003Recreate the order_items → orders foreign key constraint

Foreign keys are dropped child first: orders cannot be dropped while order_items references it through a foreign key, and customers cannot be dropped while orders references it. Restoring takes one level per migration, because restoring a single foreign key makes that parent a referenced table again.

No foreign key constraints exist between 0001 and 0003. That follows from the procedure, which routes through tables without foreign keys, not from D1. Generating a migration that contains DROP TABLE also requires turning off the tool’s own confirmation (--accept-data-loss in orm-d1 and drizzle-kit), which is a guard in the tool, not a requirement of D1 or SQLite.

How orm-d1 handles it

The migration kit in orm-d1 (why we wrote it), an ORM built only for D1, detects the case at generate time and stops.

Cannot generate a safe migration:
  - "customers" has to be recreated because a check constraint changes,
    but "orders"."customer_id" (on delete no action) references it.

It names the table to be recreated, the referencing column and its delete action. The order of the three migrations above is derived from the schema’s dependency graph.

Summary

  • Changing a CHECK means recreating the table, and the DROP TABLE in the middle is the problem
  • D1 cannot run PRAGMA foreign_keys=OFF, so a referenced table cannot be recreated
  • The change is possible by dropping and restoring foreign keys: drop child first, restore one level per migration
  • The number of tables pulled in equals the number of descendants
  • Adding an index does not rebuild the table and is unaffected

Nami Smart LLC

Fast, cost-efficient, high-quality app development. If you would like to talk about a project, please get in touch.

Contact us