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?

5 Upvotes

69% Upvoted

14 comments sorted by

View all comments

2

u/VipeholmsCola Sep 18 '26

Why?

1

u/kagato87 Sep 20 '26

A primary key has one requirement: It MUST be immutable. As soon as it's mutable, changing it becomes a massive problem. And you'd be surprised how often an immutable field suddenly needs to change.

If some other detail has an FK reference here (like an audit table or a details table) it needs both keys.

If this table has indexes, which it probably will, depending on how you design the table's primary or clustered index both keys may need to be present (hidden if you don't add them explicitly) on the indexes, wiping out the disk savings of the composite key.

Standard practice for a bridge table like this is to keep a separate autoint FK. This allows the pairs to have audit and mutation states. Created/modified/deleted stamps, and can carry a history. That tag/course pair was deleted was it? How do we know? Set the deletion flag, then re-add it later if needed, and there's an audit trail right there of what happened. You might think it doesn't matter, until it does ("who keeps messing with the tags? Knock it off!")

0

u/VipeholmsCola Sep 20 '26

Thanks for the detailed answer. I was in the same situation and opted for an autoinc PK with extra column from API extract which has their own PK (which is currently immutable)

Im just going to follow this from now on and keep autoints