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.

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.

• postgresql
• next.js13
• nextjs14
• drizzle
• drizzle-orm

• Why are you using separate tables for the correct and incorrect options. A single table can serve both purposes. Consider using a boolean column to indicate the correct answer and a unique partial index on the reference to questions when the flag is true. – JohnH Commented Feb 6 at 0:47
• @JohnH apologies the table should've been named options and not incorrectOptions. In regards to your answer I'm not sure I fully understand using a unique partial index in this case, are you able to show the code so I could understand a bit better. In prisma I was able to perform both a one to one and one to many relation between two tables which solved this issue but I can't find a solution that resembles that in drizzle. – frost2709 Commented Feb 6 at 8:25

1 Answer 1

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).