Drizzle Team•17mo ago

Strugling to use composite key in schema

How do I represent composite primary key in drizzle way (sqlite) below migration query? I red the docs but failed miserable in trying to implement it.

CREATE TABLE QuestionSetMapping (
    SetID INTEGER,
    QuestionID INTEGER,
    PRIMARY KEY(SetID, QuestionID),
    FOREIGN KEY(SetID) REFERENCES QuestionSets(SetID),
    FOREIGN KEY(QuestionID) REFERENCES Questions(QuestionID)
);

CREATE TABLE QuestionSetMapping (
    SetID INTEGER,
    QuestionID INTEGER,
    PRIMARY KEY(SetID, QuestionID),
    FOREIGN KEY(SetID) REFERENCES QuestionSets(SetID),
    FOREIGN KEY(QuestionID) REFERENCES Questions(QuestionID)
);

3 Replies

Angelelz•17mo ago

May I ask if there a reason you need the primary key to be composite? Oh wait, is this a many-to-many intermediary table?

Luxaritas•17mo ago

The docs are here: https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key The third argument to the table function allows you to create a named key using primaryKey which takes any number of columns to create the PK from

Indexes & Constraints – DrizzleORM

Drizzle ORM | %s

bkyervOP•17mo ago

Exactly, it is many to many. Thank you everyone, I just noticed that primaryKey function I have imported is from mysql instead of sqlite. Sorry for a rookie mistake

Gaming

Programming

Strugling to use composite key in schema