Is it possible to ignore foreign key conflicts on insert with sqlite?

Question

Here is an anonymized representation of two tables I have:

create table if not exists master_node (
    book_name text primary key on conflict ignore not null
);

create table if not exists category_table (
    book_name text not null,
    category text not null,
    foreign key(book_name) references master_node(book_name) on delete cascade,
    unique(book_name, category) on conflict ignore
);

when I insert code into the tables:

insert into master_node
    (book_name)
values
    ('Harry Potter'),
    ('Foundation'),
    ('The Catcher in the Rye')

and

insert or ignore into category_table
    (book_name, category)
values
    (Harry Potter', 'Fiction'),
    ('Harry Potter', 'Fantasy'),
    ('Foundation', 'Fiction'),
    ('Foundation', 'Science Fiction'),
    ('The Catcher in the Rye', 'Coming-of-age'),
    ('Moby Dick', 'Adventure')

I get a [SQLITE_CONSTRAINT] Abort due to constraint violation (FOREIGN KEY constraint failed) error and the transaction is rolled back.

I was hoping by using the insert or ignore i would be able to simply skip rows with a foreign key constraint violation. I haven't been able to find a way to get this behavior. Does sqlite provide a way to do so?

Answer 1

There is no equivalent of INSERT OR IGNORE, which works only for the violation of UNIQUE constraints, for violation of FOREIGN KEY constraints.

As a workaround you can use EXISTS in a INSERT ... SELECT statement:

WITH cte(book_name, category) AS (
    VALUES 
    ('Harry Potter', 'Fiction'),
    ('Harry Potter', 'Fantasy'),
    ('Foundation', 'Fiction'),
    ('Foundation', 'Science Fiction'),
    ('The Catcher in the Rye', 'Coming-of-age'),
    ('Moby Dick', 'Adventure')
)
INSERT INTO category_table (book_name, category)
SELECT c.book_name, c.category
FROM cte c
WHERE EXISTS (SELECT 1 FROM master_node m WHERE m.book_name = c.book_name)

See the demo.
Results:

> book_name              | category       
> :--------------------- | :--------------
> Harry Potter           | Fiction        
> Harry Potter           | Fantasy        
> Foundation             | Fiction        
> Foundation             | Science Fiction
> The Catcher in the Rye | Coming-of-age