Is it possible to insert cyclic foreign keys that are AUTO INCREMENT primary keys?

Question

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.

Answer 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.

Answer 2

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)
);