By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
PRAGMA foreign_keys = ON;
✓
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
);
✓
insert into master_node (book_name) values
('Harry Potter'),
('Foundation'),
('The Catcher in the Rye')
✓
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)
✓
SELECT * FROM category_table
| book_name | category |
|---|---|
| Harry Potter | Fiction |
| Harry Potter | Fantasy |
| Foundation | Fiction |
| Foundation | Science Fiction |
| The Catcher in the Rye | Coming-of-age |