0

I am trying to model a quiz structure, where there are questions and a unlimited number of answers, where only one is correct.

Example:

  1. Question A

    1. Answer A
    2. Answer B
    3. Answer C (Correct Answer)
    4. Answer D
  2. Question B

    1. Answer A
    2. Answer B (Correct Answer)

Given the requirements I came with the following structure:

CREATE TABLE question (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  correct_answer_id int NOT NULL
);

CREATE TABLE answer (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  question_id int NOT NULL
);

ALTER TABLE question ADD CONSTRAINT q_a_fk
  FOREIGN KEY (correct_answer_id) REFERENCES answer(id);

ALTER TABLE answer ADD CONSTRAINT a_q_fk
  FOREIGN KEY (question_id) REFERENCES question(id);

However I am having problems inserting data. Is it possible to insert this structure via a CTE or deferrable FK within a transaction?

Edit: I would like to guarantee that no question is inserted without a correct answer.

1

2 Answers 2

1

Cyclic FK constraints are always a problem.

One way to deal with this, is to fetch the "next ID" manually using nextval(). In order to not hardcode the underlying sequence names, pg_get_serial_sequence() can be used (which also works with identity columns, despite its name).

with new_ids (question_id, answer_id) as (
  select nextval(pg_get_serial_sequence('question', 'id')), 
         nextval(pg_get_serial_sequence('answer', 'id'))
), new_question as (
  insert into question (id, content, correct_answer_id)
  select question_id, 'The Question of Life, the Universe, and Everything', answer_id
  from new_ids
)
insert into answer (id, content, question_id)
select answer_id, '42', question_id
from new_ids;

Online example

But still, I would try to get rid of the cyclic dependency. In my opinion the hassle to work with this setup is a bigger problem than the fact that you can't ensure that at least one correct answer exists.

1
  • Would you still ignore that a question may not have a correct answer even if is a domain rule? In my opinion a "QuizQuestion" shouldn't be able to be inserted in the database without a correct answer. I know sometimes we can't represent all rules at database level, so my options here are: remove this cyclic FK and allow questions without correct answers (this will be a bug, need to validate at backend); have the hassle to work with cyclic FKs but have data correctness; or a different db model that only allows questions with a correct answer (which TBH I can't figure).
    – Exprove
    Commented Mar 15, 2023 at 12:57
0

You should rearrange your design so that you instead have an is_correct flag on the answer table.

You can enforce a unique correct answer by using a UNIQUE NULLS DISTINCT constraint, keeping the is_correct column either TRUE or NULL.

CREATE TABLE question (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

CREATE TABLE answer (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  question_id int NOT NULL,
  is_correct boolean NULL CHECK (is_correct = TRUE),
  UNIQUE NULLS DISTINCT (question_id, is_correct)
);

ALTER TABLE answer ADD CONSTRAINT a_q_fk
  FOREIGN KEY (question_id) REFERENCES question(id);

You could also do this with a filtered unique index, which is also useful for DBMSs which do not support NULLS DISTINCT.

For example in SQL Server:

CREATE TABLE answer (
  id int PRIMARY KEY IDENTITY,
  question_id int NOT NULL,
  is_correct bit NOT NULL,
  INDEX one_correct UNIQUE (question_id) INCLUDE (is_correct) WHERE (is_correct = 1)
);
5
  • In Postgres you would use create unique index on answer (question_id) where is_correct for the unique index (and define is_correct as NOT NULL).
    – user1822
    Commented Mar 13, 2023 at 15:38
  • This was my first approach, however I think I cannot guarantee that there is at least one correct answer (let's ignore the unique index for now) at the database level. I can create questions without correct answers.
    – Exprove
    Commented Mar 13, 2023 at 16:34
  • Yes true, you can have a deferrable constraint for that, but that brings its own issues. Commented Mar 13, 2023 at 16:48
  • @a_horse_with_no_name I know you can, but I think a NULLS DISTINCT unique constraint is cleaner. There doesn't seem to be any major difference. Commented Mar 13, 2023 at 16:50
  • 1
    Well you'll have to deal with null values in is_correct - you are essentially replacing false with null which I find much uglier
    – user1822
    Commented Mar 13, 2023 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.