When using PRAGMA defer_foreign_keys = ON I'm seeing "FOREIGN KEY constraint failed" errors on COMMIT. PRAGMA foreign_key_check reports no problems. If I set PRAGMA defer_foreign_keys = OFF before the COMMIT everything works fine.

Minimised reproduction of an attempted table migration following https://www.sqlite.org/lang_altertable.html but using defer_foreign_keys = ON rather than foreign_keys = OFF:

PRAGMA foreign_keys = 1;

CREATE TABLE Node(node_oid INTEGER PRIMARY KEY NOT NULL);
CREATE TABLE Job(
    node_oid INTEGER NOT NULL,
    FOREIGN KEY(node_oid) REFERENCES Node(node_oid));
INSERT INTO Node(node_oid) VALUES (0);
INSERT INTO Job(node_oid) VALUES (0);

BEGIN;
    PRAGMA defer_foreign_keys = ON;
    CREATE TABLE Node_migration_new(node_oid INTEGER PRIMARY KEY NOT NULL);
    INSERT INTO Node_migration_new(node_oid)
        SELECT node_oid FROM Node;
    DROP TABLE Node;
    ALTER TABLE Node_migration_new RENAME TO Node;
    PRAGMA foreign_key_check;
    -- Uncomment this and it succeeds:
    -- PRAGMA defer_foreign_keys = OFF;
COMMIT;

I'm not sure what the semantics of setting PRAGMA defer_foreign_keys = OFF. Does it do a check then and there, or does it skip checking altogether?

I've reproduced this with sqlite 3.31.1 from Ubuntu 20.04 and with a freshly built 3.39.2 (latest available from sqlite.org).