CODEWITHSUNDEEP
sqlpostgresqldatabase-designforeign-keysmany-to-many
Dave Ceddia
Dave Ceddia

Reputation: 1490

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:

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.

Upvotes: 2

Views: 152

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656401

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:

Upvotes: 6

melpomene
melpomene

Reputation: 85767

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.

Upvotes: 3

Related Questions