SQLite insertion with junction table

Question

I need to write a transaction for SQLite, that adds a blog post and links it to content tags, and it seems to be a bit too complicated for me. Here's an example schema:

CREATE TABLE blogpost(
       id INTEGER PRIMARY KEY,
       content TEXT
);

CREATE TABLE blogpost_tag(
       blogpost_id INTEGER NOT NULL,
       tag_id INTEGER NOT NULL,
       PRIMARY KEY (blogpost_id, tag_id), 
       FOREIGN KEY (blogpost_id) REFERENCES blogpost(id),
       FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE      
);

CREATE TABLE tag(
       id INTEGER PRIMARY KEY,
       name TEXT
);

I have the blog post content string and two tag strings, and now I need to write these into the database in an atomic transaction.

The blog post content row need to be inserted into blogpost table.
Let's assume that one of the tags already exists in tag table. Other one is new and needs to be inserted.
Blogposts and tags have a many-to-many relationship, so I need to insert two rows to blogpost_tag table.

How do I write this transaction?

SQLite insertion with junction table

0 Answers0