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?

What is the point of having a foreign key constraint if you are going to violate it? If you want to add records that don't exist in `master_node` than remove the constraint. — Ivar, Dec 29 '20 at 19:31
`insert or ignore` ignores only UNIQUE constraint violations. — forpas, Dec 29 '20 at 19:35
@Ivar i want the FK constraint to keep me from adding rows that violate the constraint. I only want rows that have a `book_name` from the `master_node` but if I'm doing a bulk insert and i have rows that violate the constraint i just want to ignore those rows, not roll back the whole insert. — Grant Williams, Dec 29 '20 at 19:38
@forpas yeah, I noticed it only works on `unique` and `check`, I'm just looking for the same functionality but for foreign key constraints instead. — Grant Williams, Dec 29 '20 at 19:38
Does this answer your question? [insert or ignore rows that violate foreign key constraints sqlite](https://stackoverflow.com/questions/65336356/insert-or-ignore-rows-that-violate-foreign-key-constraints-sqlite) — astentx, Dec 29 '20 at 19:47
@astentx I can probably make an `exists` check work, but I was hoping for an easier way using the FKs themselves. I have 4 other tables that reference the `master_node` table and I'll be cascading deletes from it using the FKs. It does look like I don't have much of a choice though. — Grant Williams, Dec 29 '20 at 19:53

score 2 · Accepted Answer · answered Dec 29 '20 at 19:53

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

Thanks. I was hoping to be able to use the FKs since I need them for cascading deletes anyway, but this solution looks to be the cleanest way to solve my problem. — Grant Williams, Dec 29 '20 at 19:55

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

1 Answers1