If you enter question and answer in a single statement with a data-modifying CTE, you do not even need a DEFERRABLE FK constraints. Not to speak of actually making (or SETting) them DEFERRED - which would be a lot more expensive.
Data model
First I cleaned up your data model:
CREATE TABLE question (
question_id serial PRIMARY KEY
, correct_answer_id int NOT NULL
, question text NOT NULL
, solution text NOT NULL
);
CREATE TABLE answer (
answer_id serial PRIMARY KEY
, question_id int NOT NULL REFERENCES question
, answer text NOT NULL
);
ALTER TABLE question ADD CONSTRAINT question_correct_answer_id_fkey
FOREIGN KEY (correct_answer_id) REFERENCES answer(answer_id);
- Don't use the non-descriptive "id" or "text" (also a basic type name) as column names.
- Put integer columns first for space efficiency. See:
bigint was uncalled for, integer should suffice.- Simplify your schema definition with
serial columns.
- Define primary keys. PK columns are
NOT NULL automatically.
Solution
After delegating primary key generation to sequences (serial columns), we can get auto-generated IDs with the RETURNING clause of the INSERT statement. But in this special case we need both IDs for each INSERT, so I fetch one of them with nextval() to get it going.
WITH q AS (
INSERT INTO question
(correct_answer_id , question, solution)
VALUES (nextval('answer_answer_id_seq'), 'How?' , 'DEFERRABLE FK & CTE')
RETURNING correct_answer_id, question_id
)
INSERT INTO answer
(answer_id , question_id, answer)
SELECT correct_answer_id, question_id, 'Use DEFERRABLE FK & CTE'
FROM q;
I know the name of the sequence ('answer_answer_id_seq') because I looked it up. It's the default name. If you don't know it use the safe form @IMSoP provided in a comment:
nextval(pg_get_serial_sequence('answer', 'answer_id'))
DEFERRABLE or DEFERRED constraints?
The manual on SET CONSTRAINTS:
IMMEDIATE constraints are checked at the end of each statement.
My solution is a single statement. That's why it works where two separate statements would fail - wrapped in a single transaction or not. And you'd need SET CONSTRAINTS ... DEFERRED; like IMSoP first commented and @Jaaz implemented in his answer.
However, note the disclaimer some paragraphs down:
Uniqueness and exclusion constraints that have not been declared
DEFERRABLE are also checked immediately.
So UNIQUE and EXCLUDE need to be DEFERRABLE to make CTEs work for them. This includes PRIMARY KEY constraints. The documentation on CREATE TABLE has more details:
Non-deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
checks for uniqueness immediately whenever a row is inserted or
modified. The SQL standard says that uniqueness should be enforced
only at the end of the statement; this makes a difference when, for
example, a single command updates multiple key values. To obtain
standard-compliant behavior, declare the constraint as DEFERRABLE but
not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
significantly slower than immediate uniqueness checking.
We discussed this in great detail under this related question:
