Search code examples
sqlpostgresqldatabase-designforeign-keysmany-to-many

Enforce relationship via a foreign key constraint?

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.

Solution

  • 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: