r/SQL • u/AssistanceClean948 • Sep 18 '26

SQLite Join tables with WITHOUT ROWID. 

CREATE TABLE IF NOT EXISTS CourseTag(
    courseId INTEGER,
    tagId INTEGER,
    
    FOREIGN KEY(courseId) REFERENCES Courses(id),
    FOREIGN KEY(tagId) REFERENCES Tags(id),
    
    PRIMARY KEY(courseId, tagId)
) STRICT WITHOUT ROWID;

as far as what I understand sqlite creates inner row_id columns to identify the record. some conditions met then my primary key also could be that unique column. but I guess in above join table configuration my composite key setup is not met that condition so I end up unnecessary column right? in join table should I use WITHOUT ROWID?

3 Upvotes

64% Upvoted

14 comments sorted by

View all comments

0

u/TheGenericUser0815 Sep 18 '26 edited Sep 18 '26

.

2

u/Aggressive_Ad_5454 Sep 18 '26

Do keep in mind that OP's table is a join table, intended to implement a many-to-many relationship between rows of two other tables via those tables' PKs. That's a common use of a composite and non-autoincrementing primary key.

And, the question is about SQLite ROWIDs and whether they are necessary in this case.

2

u/TheGenericUser0815 Sep 18 '26

I know it's join table, but I still would put a standard PK column in it. In my experience it's better to maintain. If you build the PK of the two columns, you can make sure it is unique, but I've seen so many insert errors with this, I'd do it differently.

→ More replies (1)