2

Let's say I have the following tables:

Questions, Choices, Answers

Each Question has many Choices (Choices has a question_id foreign key.)

The Answers table has two foreign keys, question_id and choice_id, and these constraints prevent answers that refer to a non-existent question or choice, but I want to prevent invalid choices, too.

How could I express a constraint (maybe that's not the right word) that only allows answers where the choice_id is a valid choice for the given question_id? For example, if I had 2 questions, each with 2 choices:

  • Q1 (id = 1)
    • A (id = 1)
    • B (id = 2)
  • Q2 (id = 2)
    • C (id = 3)
    • D (id = 4)

I want to allow answers like (question_id = 1, choice_id = 1 (or 2)) and prevent answers like (question_id = 1, choice_id = 4) because that's not a valid choice for the question.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dave Ceddia
  • 1,480
  • 2
  • 17
  • 24
  • I struggle to understand what the difference between a choice and an answer would be here. Is answer the choice a user picks for a question? – sticky bit May 12 '19 at 21:45
  • Yeah, maybe they could use better names, but the "answer" is the user's submission, and the "choices" are the valid options for a question (a multiple-choice one). Got any ideas for better names? – Dave Ceddia May 12 '19 at 21:56
  • It looks to me like choices will have two different ways of identifying them, the choiceId which will be unique across all choices of a ll questions, and the Choice number the test taker sees, which will be 1 through 5 for all of the questions. You need to be careful not to confuse these two, or else you will never get the foreign key constraints right. – Walter Mitty May 12 '19 at 22:12
  • @WalterMitty Indeed! I'll need to be careful of that. I'm storing an "order" property on a few tables to keep track of their index in each question. – Dave Ceddia May 13 '19 at 01:51

2 Answers2

6

All you need is a FK constraint from answer to choice - the row in choice points to the one applicable question in return.

If you insist on having a column answer.question_id redundantly (there are corner cases where this makes sense) still only have a single, multicolumn FK constraint spanning both columns. This requires a matching multicolumn UNIQUE (or PK) constraint on table choice first (also typically redundant otherwise):

ALTER TABLE choice ADD CONSTRAINT choice_uni UNIQUE (question_id, choice_id);

Then:

ALTER TABLE answer ADD constraint answer_choice_fkey
FOREIGN KEY (question_id, choice_id)  REFERENCES choice(question_id, choice_id);

All involved columns are NOT NULL or you may have to do more: start by learning about the different match types of foreign key constraints in this case:

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

Why does your "answers" table have a question_id?

If it only has a choice_id, it will always refer to an existing choice. The choice itself links to a valid question, uniquely determining which question a given answer is for.

melpomene
  • 84,125
  • 8
  • 85
  • 148
  • That's... a very good point :) Avoiding the problem entirely, I like it. – Dave Ceddia May 12 '19 at 21:53
  • You don't even actually need an "answer" table at all. Only add a boolean "valid_answer" column to you `choices` table. Using a relation table here would be useful only if the large majority of questions doesn't have an answer at all, but only a few subset would. – Obsidian May 12 '19 at 21:58
  • @Obsidian "Answer" is not a relation table. – melpomene May 12 '19 at 22:00
  • @Obsidian The use case here is for multiple users to be able to submit answers to multiple questions, where the "answers" table will have one row per response. – Dave Ceddia May 12 '19 at 22:05
  • @Dave, I agree but you still don't need a dedicated "answer" table to do that. To be precise, this won't bring you more that a flag on questions in this case. In particular, this won't help you to restrict the content to at most one valid answer per question… – Obsidian May 12 '19 at 22:16
  • @Obsidian Your comments make no sense. I don't think you understand the data OP is trying to store. – melpomene May 12 '19 at 22:19
  • @Obsidian There'll be many many more responses (answers) than there are choices or questions. The choices/questions are essentially static, or very rarely changing, but multiple users will take this survey so there'll be hundreds or thousands of answers. – Dave Ceddia May 13 '19 at 01:54