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.

Improve this question
4
  • 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? Commented May 12, 2019 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? Commented May 12, 2019 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. Commented May 12, 2019 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. Commented May 13, 2019 at 1:51

2 Answers 2

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

7 Comments

That's... a very good point :) Avoiding the problem entirely, I like it.
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 "Answer" is not a relation table.
@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, 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…
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.