I have a model that defines mutually recursive tables:
CREATE TABLE answer (
id integer NOT NULL, -- answer id
text character varying NOT NULL, -- answer text
question_id bigint NOT NULL -- question id
CREATE TABLE question (
id integer NOT NULL, -- question id
question character varying NOT NULL, -- question text
correct bigint NOT NULL, -- correct answer
solution character varying NOT NULL -- solution text
ALTER TABLE ONLY answer ALTER COLUMN id SET DEFAULT nextval('answer_id_seq'::regclass);
ALTER TABLE ONLY answer
ADD CONSTRAINT answer_question_id_fkey FOREIGN KEY (question_id) REFERENCES question(id);
ALTER TABLE ONLY question ALTER COLUMN id SET DEFAULT nextval('question_id_seq'::regclass);
ALTER TABLE ONLY question
ADD CONSTRAINT question_correct_fkey FOREIGN KEY (correct) REFERENCES answer(id);
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
DEFERRED - which would be a lot more expensive.
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);
textas column name.
bigintwas uncalled for,
I delegated primary key generation to sequences (
serial columns) like it should be in most data models. We can get the auto-generated ID 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 the thing started.
WITH q AS ( INSERT INTO question (correct_answer_id, question, solution) VALUES (nextval('answer_answer_id_seq'), 'How?', 'DEFERRABLE FK & wCTE') RETURNING correct_answer_id, question_id ) INSERT INTO answer (answer_id, question_id, answer) SELECT correct_answer_id, question_id, 'Use DEFERRABLE FK & data-modifying 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 the comment below:
IMMEDIATEconstraints 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
DEFERRABLEare also checked immediately.
EXCLUDE need to be
DEFERRALBE to make wCTEs work for them. This includes
PRIMARY KEY constraints. The documentation on
CREATE TABLE has more details:
Non-deferred Uniqueness Constraints
PRIMARY KEYconstraint 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
DEFERRABLEbut 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: