tl;dr - You likely need PRAGMA defer_foreign_keys = on; and these docs.

While writing a SQL migration for a Durable Object I kept hitting this frustrating error:

FOREIGN KEY constraint failed: SQLITE_CONSTRAINT

I had something similar to the following:

CREATE TABLE A (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE B (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  id2 INTEGER,
  book TEXT,
  FOREIGN KEY(id2) REFERENCES A(id)
);

I wanted to add a CHECK constraint to a column in table A which can be done by renaming and copying:

CREATE TABLE new_A (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL CHECK (type IN ('image', 'html', 'pdf'))
);

INSERT INTO new_A
SELECT
  id,
  name
FROM A;

DROP TABLE A;

ALTER TABLE new_A RENAME TO A;

Note that this invariant was already enforced by the app. This would not be safe to do in general.

However this won’t work, and you’ll get the FOREIGN KEY constraint failed error.

The SQLite docs will lead you to:

PRAGMA foreign_keys = false

-- Do the migration

PRAGMA foreign_keys = true

However this will not work either. Though frustratingly it will if you dig into the .wrangler directory, find the sqlite db file, and try applying it directly.

The solution is in the Cloudflare docs. The doc is for D1 but this applies to SQLite in Durable Objects as well.

D1’s foreign key enforcement is equivalent to SQLite’s PRAGMA foreign_keys = on directive. Because D1 runs every query inside an implicit transaction, user queries cannot change this during a query or migration.

D1 allows you to call PRAGMA defer_foreign_keys = on or off, which allows you to violate foreign key constraints temporarily (until the end of the current transaction).

We can now fix our migration!

PRAGMA defer_foreign_keys = on;

CREATE TABLE new_A (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL CHECK (type IN ('image', 'html', 'pdf'))
);

INSERT INTO new_A
SELECT
  id,
  name
FROM A;

DROP TABLE A;
ALTER TABLE new_A RENAME TO A;

PRAGMA defer_foreign_keys = off;