postgresqlnext.js13nextjs14drizzledrizzle-orm

Disambiguating one to one relations


I have two tables, Questions and Options

export const QuestionTable = pgTable("questions", {
  id,
  question: varchar().notNull(),

  ...timestamps,
});

export const OptionTable = pgTable("options", {
  id,
  text: varchar().notNull(),
  value: varchar().notNull(),

  ...timestamps,
});

I want more than one relation between these tables. Each question should have one correct option and multiple incorrect options. This means a one-to-one relation between question and option and a many-to-one relation between them also. The docs has an example for disambiguating relations but both referenced fields are one to many relations and I need both a one to many and one to one relation. There’s a known bug flagged already here where you can’t use the relation name on one to one relations in multiple relations. Does anyone know how to solve this?

I’ve tried manipulating the example in the docs and I’ve consulted the assistant on drizzles website to help to no avail.


Solution

  • You do not need separate tables for the correct option and the incorrect options, as indicated by JohnH. Create a single table containing all options (correct and incorrect) and a boolean indicating which option is correct. Then create a partial unique index on the question id and that boolean. I do not know your obscurification language (Drizzle) but the following shows creation and use of a partial unique index. (full demo here)
    Sample table and partial unique index creation:

    create table question_options( opt_id   integer generated always as identity
                                            primary key
                                 , question_id integer not null 
                                            references questions(id) 
                                 , tag     text not null 
                                 , label   text not null 
                                 , correct boolean not null default false
                                 );
    
    create unique index "Can have only one Correct Answer" on question_options(question_id, correct) where correct;
    

    This would allow any number rows with the combination of (question_id, false) but only a single row of (question_id, true).