Can / how to add a circle of links without breaking RI?

Question

I was primarily interested in pgsql, but I was wondering if there is a way in any DBMS to perform an insert operation without disconnecting and re-enabling any FOREIGN KEY or NOT NULL constraints on two tables that reference each other. (You might think of it as a chicken that was somehow born from its own egg.)

For a practical example, if you had a multiple choice survey system with question and answer tables, where question.correct_answer refers to answer.id and answer.question refers to question.id, you can add a question and it answers at the same time?

(For the record, I know that you can disable and re-enable in the transaction block, and the other solution is to not have the correct_answer column, and instead have answer.correct as logical and have the restriction checked, making sure that there is only one correct one answer. But I'm not interested in alternative solutions here.)

Answer 1

I think that you answered your question - you need 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\ legs?');
INSERT INTO answers (answerid, questionid, answer)
  VALUES (100, 1, 'Long enough to reach the ground.');
COMMIT;

It must be in the transaction block, because if the INSERT statement fails, the database will be in an invalid state (table restrictions are not met).

Answer 2

I would do it like this:

• Questions.correct_answer .
• "", correct_answer NULL.
• () , .
• SET correct_answer =?

Answer 3

In the simple case of one question and one answer, it is desirable to simply put all the attributes in one table.