Meet Gor
@meetgor.bsky.social
Software developer | Living in the terminal, travelling to databases and the web with CLIs and APIs
Blog: https://meetgor.com
Newsletter: https://techstructively.substack.com/
Blog: https://meetgor.com
Newsletter: https://techstructively.substack.com/
Many to Many Relation
We saw the basic example of www.meetgor.com/sqlog/sqlite... Relation in the second last post from this, there we just focused on the concept of the relation and not so much on the structure of the junction table.
Let's take a look at the schema again:
We saw the basic example of www.meetgor.com/sqlog/sqlite... Relation in the second last post from this, there we just focused on the concept of the relation and not so much on the structure of the junction table.
Let's take a look at the schema again:
SQLite SQL: Many to Many Relations | Meet Gor
www.meetgor.com
September 20, 2025 at 11:54 AM
Read more here for interactive SQL codeblocks and playground like environment:
www.meetgor.com/sqlog/sqlite...
www.meetgor.com/sqlog/sqlite...
SQLite SQL: Many to Many Table without RowID | Meet Gor
www.meetgor.com
September 20, 2025 at 11:51 AM
Read more here for interactive SQL codeblocks and playground like environment:
www.meetgor.com/sqlog/sqlite...
www.meetgor.com/sqlog/sqlite...
Only in the case of author_post or junction table, where the primary key is used as a combination of other two foreign keys, we don't need the rowid as the junction table doesn't have any unique data in it, it is just the mapping of two existing data records in the other two tables in the database
September 20, 2025 at 11:51 AM
Only in the case of author_post or junction table, where the primary key is used as a combination of other two foreign keys, we don't need the rowid as the junction table doesn't have any unique data in it, it is just the mapping of two existing data records in the other two tables in the database
Both the tables users and posts will have rowid referenced or aliased as id of their respective columns as we know because of the special case of INTEGER PRIMARY KEY
SELECT rowid, _rowid_, * FROM users;
SELECT rowid, _rowid_, * FROM posts;
SELECT rowid, _rowid_, * FROM users;
SELECT rowid, _rowid_, * FROM posts;
September 20, 2025 at 11:51 AM
Both the tables users and posts will have rowid referenced or aliased as id of their respective columns as we know because of the special case of INTEGER PRIMARY KEY
SELECT rowid, _rowid_, * FROM users;
SELECT rowid, _rowid_, * FROM posts;
SELECT rowid, _rowid_, * FROM users;
SELECT rowid, _rowid_, * FROM posts;
This would return error as the rowid doesn't exists.
We will still need the rowid in posts and users table as those are the PRIMARY KEY columns.
We will still need the rowid in posts and users table as those are the PRIMARY KEY columns.
September 20, 2025 at 11:51 AM
This would return error as the rowid doesn't exists.
We will still need the rowid in posts and users table as those are the PRIMARY KEY columns.
We will still need the rowid in posts and users table as those are the PRIMARY KEY columns.
The rest of the commands from inserts to selection and all of the things we saw above will remain the same.
However, when you query the author_post table, the rowid will not be returned as it doesn't exists.
SELECT * FROM author_post;
SELECT rowid, * FROM author_post;
However, when you query the author_post table, the rowid will not be returned as it doesn't exists.
SELECT * FROM author_post;
SELECT rowid, * FROM author_post;
September 20, 2025 at 11:51 AM
The rest of the commands from inserts to selection and all of the things we saw above will remain the same.
However, when you query the author_post table, the rowid will not be returned as it doesn't exists.
SELECT * FROM author_post;
SELECT rowid, * FROM author_post;
However, when you query the author_post table, the rowid will not be returned as it doesn't exists.
SELECT * FROM author_post;
SELECT rowid, * FROM author_post;
CREATE TABLE author_post (user_id INTEGER NOT NULL,post_id INTEGER NOT NULL, PRIMARY KEY (user_id, post_id), FOREIGN KEY (user_id) REFERENCES users (id),FOREIGN KEY (post_id) REFERENCES posts (id) )
WITHOUT ROWID;
WITHOUT ROWID;
September 20, 2025 at 11:51 AM
CREATE TABLE author_post (user_id INTEGER NOT NULL,post_id INTEGER NOT NULL, PRIMARY KEY (user_id, post_id), FOREIGN KEY (user_id) REFERENCES users (id),FOREIGN KEY (post_id) REFERENCES posts (id) )
WITHOUT ROWID;
WITHOUT ROWID;
Hence, we can avoid creating the rowid using the WITHOUT ROWID parameter or option while creating the table.
DROP TABLE author_post;
DROP TABLE author_post;
September 20, 2025 at 11:51 AM
Hence, we can avoid creating the rowid using the WITHOUT ROWID parameter or option while creating the table.
DROP TABLE author_post;
DROP TABLE author_post;
The answer is very naive and subtle, but might impact the database querying if the author_post has millions and billions of rows. The rowid space will be wasted for no reason.
September 20, 2025 at 11:51 AM
The answer is very naive and subtle, but might impact the database querying if the author_post has millions and billions of rows. The rowid space will be wasted for no reason.
This table author_post has primary key identified from the combination of the user_id and post_id column. However, storing rowid makes no sense, as it is just redundant, right?
Why would you not want to have the rowid?
Why would you not want to have the rowid?
September 20, 2025 at 11:51 AM
This table author_post has primary key identified from the combination of the user_id and post_id column. However, storing rowid makes no sense, as it is just redundant, right?
Why would you not want to have the rowid?
Why would you not want to have the rowid?
This has 5 posts, two written by Pekka, two written by Glauber, and one co-authored by both Pekka and Glauber.
The interesting part here is this rowid
SELECT rowid, * FROM author_post;
The interesting part here is this rowid
SELECT rowid, * FROM author_post;
September 20, 2025 at 11:51 AM
This has 5 posts, two written by Pekka, two written by Glauber, and one co-authored by both Pekka and Glauber.
The interesting part here is this rowid
SELECT rowid, * FROM author_post;
The interesting part here is this rowid
SELECT rowid, * FROM author_post;
This has now populated the following tables:
SELECT p. id, p.content AS post, GROUP_CONCAT(u. name, ', ') AS authors FROM posts p JOIN author_post up ON p. id = up. post_id JOIN users u ON u. id = up.user_id GROUP BY p. id;
SELECT p. id, p.content AS post, GROUP_CONCAT(u. name, ', ') AS authors FROM posts p JOIN author_post up ON p. id = up. post_id JOIN users u ON u. id = up.user_id GROUP BY p. id;
September 20, 2025 at 11:51 AM
This has now populated the following tables:
SELECT p. id, p.content AS post, GROUP_CONCAT(u. name, ', ') AS authors FROM posts p JOIN author_post up ON p. id = up. post_id JOIN users u ON u. id = up.user_id GROUP BY p. id;
SELECT p. id, p.content AS post, GROUP_CONCAT(u. name, ', ') AS authors FROM posts p JOIN author_post up ON p. id = up. post_id JOIN users u ON u. id = up.user_id GROUP BY p. id;
-- adding a post co-authored by pekka and glauber
INSERT INTO posts(title, content) VALUES('Limbo', 'SQLite in Rust');
INSERT INTO author_post(user_id, post_id) VALUES (3, 5);
INSERT INTO author_post(user_id, post_id) VALUES (1, 5);
INSERT INTO posts(title, content) VALUES('Limbo', 'SQLite in Rust');
INSERT INTO author_post(user_id, post_id) VALUES (3, 5);
INSERT INTO author_post(user_id, post_id) VALUES (1, 5);
September 20, 2025 at 11:51 AM
-- adding a post co-authored by pekka and glauber
INSERT INTO posts(title, content) VALUES('Limbo', 'SQLite in Rust');
INSERT INTO author_post(user_id, post_id) VALUES (3, 5);
INSERT INTO author_post(user_id, post_id) VALUES (1, 5);
INSERT INTO posts(title, content) VALUES('Limbo', 'SQLite in Rust');
INSERT INTO author_post(user_id, post_id) VALUES (3, 5);
INSERT INTO author_post(user_id, post_id) VALUES (1, 5);
-- adding posts written by glauber
INSERT INTO posts(title, content) VALUES ('Rewriting SQLite', 'We are no more a sqlite-fork');
INSERT INTO posts(title, content) VALUES ('Offline Writes in SQLite', 'Lets sync');
INSERT INTO author_post(user_id, post_id) VALUES (1, 3), (1, 4);
INSERT INTO posts(title, content) VALUES ('Rewriting SQLite', 'We are no more a sqlite-fork');
INSERT INTO posts(title, content) VALUES ('Offline Writes in SQLite', 'Lets sync');
INSERT INTO author_post(user_id, post_id) VALUES (1, 3), (1, 4);
September 20, 2025 at 11:51 AM
-- adding posts written by glauber
INSERT INTO posts(title, content) VALUES ('Rewriting SQLite', 'We are no more a sqlite-fork');
INSERT INTO posts(title, content) VALUES ('Offline Writes in SQLite', 'Lets sync');
INSERT INTO author_post(user_id, post_id) VALUES (1, 3), (1, 4);
INSERT INTO posts(title, content) VALUES ('Rewriting SQLite', 'We are no more a sqlite-fork');
INSERT INTO posts(title, content) VALUES ('Offline Writes in SQLite', 'Lets sync');
INSERT INTO author_post(user_id, post_id) VALUES (1, 3), (1, 4);
-- adding posts written by pekka INSERT INTO posts(title, content) VALUES ('Switching to Zig from Rust', 'I love C');
INSERT INTO posts(title, content) VALUES ('RAG in SQLite', 'AI first database');
INSERT INTO author_post(user_id, post_id) VALUES (3, 1), (3, 2);
INSERT INTO posts(title, content) VALUES ('RAG in SQLite', 'AI first database');
INSERT INTO author_post(user_id, post_id) VALUES (3, 1), (3, 2);
September 20, 2025 at 11:51 AM
-- adding posts written by pekka INSERT INTO posts(title, content) VALUES ('Switching to Zig from Rust', 'I love C');
INSERT INTO posts(title, content) VALUES ('RAG in SQLite', 'AI first database');
INSERT INTO author_post(user_id, post_id) VALUES (3, 1), (3, 2);
INSERT INTO posts(title, content) VALUES ('RAG in SQLite', 'AI first database');
INSERT INTO author_post(user_id, post_id) VALUES (3, 1), (3, 2);
Let's now populate the tables.
-- adding authors/users
INSERT INTO users(name) VALUES ('Glauber'), ('Jamie'), ('Pekka');
-- adding authors/users
INSERT INTO users(name) VALUES ('Glauber'), ('Jamie'), ('Pekka');
September 20, 2025 at 11:51 AM
Let's now populate the tables.
-- adding authors/users
INSERT INTO users(name) VALUES ('Glauber'), ('Jamie'), ('Pekka');
-- adding authors/users
INSERT INTO users(name) VALUES ('Glauber'), ('Jamie'), ('Pekka');
CREATE TABLE author_post (user_id INTEGER NOT NULL,post_id INTEGER NOT NULL,PRIMARY KEY (user_id, post_id),FOREIGN KEY (user_id) REFERENCES users (id),FOREIGN KEY (post_id) REFERENCES posts (id) );
September 20, 2025 at 11:51 AM
CREATE TABLE author_post (user_id INTEGER NOT NULL,post_id INTEGER NOT NULL,PRIMARY KEY (user_id, post_id),FOREIGN KEY (user_id) REFERENCES users (id),FOREIGN KEY (post_id) REFERENCES posts (id) );
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL );
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL,content TEXT NOT NULL );
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL,content TEXT NOT NULL );
September 20, 2025 at 11:51 AM
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL );
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL,content TEXT NOT NULL );
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL,content TEXT NOT NULL );
One to One Relations
I might missed this basic relationship model as I haven't really found it quite commonly used, but its still used in very specific examples.
That one relationship is one-to-one. As the name suggests, it maps one row to exactly one row.
I might missed this basic relationship model as I haven't really found it quite commonly used, but its still used in very specific examples.
That one relationship is one-to-one. As the name suggests, it maps one row to exactly one row.
September 19, 2025 at 4:07 PM
Want to read the full post with interactive SQL codeblocks and playground?
head here:
www.meetgor.com/sqlog/sqlite...
head here:
www.meetgor.com/sqlog/sqlite...
SQLite SQL: One to One Relations | Meet Gor
www.meetgor.com
September 19, 2025 at 4:04 PM
Want to read the full post with interactive SQL codeblocks and playground?
head here:
www.meetgor.com/sqlog/sqlite...
head here:
www.meetgor.com/sqlog/sqlite...
Creating a One to One Relation
How do we define a one to one relation, if we add a foreign key to the table that can refer multiple entities, so maybe if that foreign key is the primary key?
Ok, let me explain more clearly.
How do we define a one to one relation, if we add a foreign key to the table that can refer multiple entities, so maybe if that foreign key is the primary key?
Ok, let me explain more clearly.
September 19, 2025 at 4:04 PM
Creating a One to One Relation
How do we define a one to one relation, if we add a foreign key to the table that can refer multiple entities, so maybe if that foreign key is the primary key?
Ok, let me explain more clearly.
How do we define a one to one relation, if we add a foreign key to the table that can refer multiple entities, so maybe if that foreign key is the primary key?
Ok, let me explain more clearly.
You can definitely restrict the one-to-many relation to get this done, but might be a little wired. Will check that in other post.
September 19, 2025 at 4:04 PM
You can definitely restrict the one-to-many relation to get this done, but might be a little wired. Will check that in other post.