How to fix SQLITE_CONSTRAINT_FOREIGNKEY: FOREIGN KEY constraint failed in SQLite

PRAGMA foreign_keys = ON;

const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');
db.pragma('foreign_keys = ON');

import sqlite3
conn = sqlite3.connect('mydb.sqlite')
conn.execute('PRAGMA foreign_keys = ON')

PRAGMA foreign_keys;  -- Should return 1

PRAGMA foreign_key_check;

PRAGMA foreign_key_check(table_name);

-- CORRECT: Parent first, then child
INSERT INTO authors (id, name) VALUES (1, 'Jane Doe');
INSERT INTO books (id, author_id, title)
  VALUES (1, 1, 'My Book');

-- WRONG: Child before parent causes error
INSERT INTO books (id, author_id, title)
  VALUES (1, 999, 'My Book');  -- author_id 999 doesn't exist!

BEGIN TRANSACTION;
  INSERT INTO authors (id, name) VALUES (1, 'Jane Doe');
  INSERT INTO books (id, author_id, title) VALUES (1, 1, 'My Book');
COMMIT;

-- CORRECT: Delete children first
DELETE FROM books WHERE author_id = 1;
DELETE FROM authors WHERE id = 1;

-- WRONG: Deleting parent first causes error if children exist
DELETE FROM authors WHERE id = 1;  -- Fails if books reference this author!

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER,
  title TEXT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE CASCADE  -- Automatically delete child books
);

-- Check if author exists before inserting book
SELECT id FROM authors WHERE id = 5;

-- Only insert if author exists
INSERT INTO books (author_id, title)
  SELECT 5, 'New Book'
  WHERE EXISTS (SELECT 1 FROM authors WHERE id = 5);

-- Update only if new author_id exists
UPDATE books
SET author_id = 10
WHERE id = 1
  AND EXISTS (SELECT 1 FROM authors WHERE id = 10);