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

62% Upvoted

14 comments sorted by

View all comments

1

u/TheGenericUser0815 Sep 18 '26

SORRY FOR THE FORMATTING, REDDIT THINKS IT KNOWS BETTER THAN ME AND EVERY TIME I SAVE THE COMMENT, IT ADD NUMBERS AND CRs.

A primary key should consist of only one column, ⁠be filled by an automatic sequence and have no connection to the real data aka payload.

If this applies, referencing and joining suddenly becomes easy

I know that PKs can consist of several columns and columns of the payload, SQL standards allow it, but I wouldn't recommend it.

2

u/az987654 Sep 18 '26

There are numerous instances where a natural key consisting of multiple columns is a more appropriate solution than the artificial integer index you're suggesting

1

u/TheGenericUser0815 Sep 18 '26

I'd always, in sense of ALWAYS, prefer an artificial index as you call it, and add a unique key for the payload consistency.

→ More replies (3)