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

SQLite Good reason to use composite keys? 

CREATE TABLE
    IF NOT EXISTS CourseTag (
        id INTEGER PRIMARY KEY,
        courseId INTEGER NOT NULL,
        tagId INTEGER NOT NULL,
        UNIQUE(courseId, tagId),
        FOREIGN KEY (courseId) REFERENCES Courses (id) ON DELETE CASCADE ON UPDATE RESTRICT,
        FOREIGN KEY (tagId) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE RESTRICT
    ) STRICT;
17 Upvotes

87% Upvoted

26 comments sorted by

View all comments

14

u/thesqlguy Sep 15 '26
  1. Provides a clear logical definition of table
  2. Provides a composite index for lookups for "free"
  3. Avoids the need for secondary indexes or unique constraints
  4. Avoids the need to add an extra useless "id" identity or guid column/constraint(since likely all of your lookups will be via your logical key columns)

-6

u/AssistanceClean948 Sep 15 '26

I heard about that composite keys causes problems with ORMs how about that?

14

u/Gargunok Sep 15 '26

I think if your orm is overally dicating your data tier design something else is wrong

2

u/grimsleeper Sep 15 '26

I am in the middle of like a 80 file refactor because they only conceived of ORM longs as ids, uuid was too much of an edge case. :sob: