Can/how do you add a circle of references without breaking RI?

Question

I'm primarily interested in pgsql for this, but I was wondering if there is a way in any RDBMS to do an insert operation, without disabling and re-enabling any FOREIGN KEY or NOT NULL constraints, on two tables that refer to each other. (You might think of this as a chicken that was somehow born from its own egg.)

For a practical example, if you had a multiple-choice quiz system, with tables "question" and "answer", where question.correct_answer refers to answer.id, and answer.question refers to question.id, is it possible to add a question and its answers simultaneously?

(For the record, I'm aware that you can do the disabling and re-enabling in a transaction block, and that another solution is to not have a correct_answer column but instead have answer.correct as a boolean and have a check constraint making sure there's exactly one correct answer per question. But I'm not curious about alternative solutions here.)

Answer 1

I think that you answered your own question - you have to make a transaction block. In PostgreSQL this should work:

BEGIN;
  SET CONSTRAINTS ALL DEFERRED;
INSERT INTO questions (questionid, answerid, question)
  VALUES (1, 100, 'How long are Abraham Lincoln\'s legs?');
INSERT INTO answers (answerid, questionid, answer)
  VALUES (100, 1, 'Long enough to reach the ground.');
COMMIT;

It has to be in a transaction block because if either INSERT statement failed the database would be in an invalid state (table constraints not met).

Answer 2

I'd do it in the following way:

1. Define Questions.correct_answer as a nullable column.
2. Insert row into Questions, with correct_answer set to NULL.
3. Insert row(s) into Answers, referencing the row in Questions.
4. UPDATE Questions SET correct_answer = ?

Answer 3

In the simple case of one question and one answer it is advisable to just put all attributes into one table.