如何处理相互递归的插入

Question

我有一个定义相互递归表的模型：

Answer
  questionId QuestionId
  text

Question
  text
  correct AnswerId

要实际插入问题，我需要怎么做？我需要知道什么是正确的答案。但是要插入答案，我需要知道答案是什么。

如果有问题，我正在运行Postgres。

DDL为：

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

Answer 1

如果您使用数据修改CTE在 单个语句中
输入问题和答案，那么您甚至都不需要FK约束。更不用说实际制造（或发声）它们了-
这会贵得多。DEFERRABLE``SET``DEFERRED

资料模型

首先，我清理了您的数据模型：

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

• 不要使用非描述性的“ id”作为列名。
• 不要使用基本类型名text作为列名。
• 为了节省空间，将整数列放在第一位：
  • 在PostgreSQL中计算并节省空间
• bigint没必要，integer就足够了。
• 使用serialcolumn简化您的架构定义。
• 定义主键。PK列是NOT NULL自动的。

解决方案

我委派主键生成到序列（serial列）像它 应该 在大多数数据模型。我们可以使用语句的RETURNING子句获取自动生成的ID
INSERT。但是在这种特殊情况下，我们每个人 都 需要 两个 ID INSERT，因此我nextval()开始使用其中的一个ID 。

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;

我 知道 序列（'answer_answer_id_seq'）的名称，因为我查找了它。这是默认名称。如果您不 知道
，请使用以下评论中提供的安全格式@IMSoP：

nextval(pg_get_serial_sequence('answer', 'answer_id'))

DEFERRABLE或DEFERRED约束？

每个文档上 SET CONSTRAINTS

IMMEDIATE 在每个语句的末尾检查约束。

我的解决方案是一个 单一的 声明。这就是为什么它可以在两个单独的语句失败的地方工作的原因-是否包装在单个事务中。而且你需要SET CONSTRAINTS ...DEFERRED;像IMSOP第一评论和@Jaaz在他的回答中实现。 但是，请注意免责声明的某些段落：

DEFERRABLE还可以立即检查尚未声明的唯一性和排除约束 。

因此UNIQUE，EXCLUDE需要DEFERRALBE使wCTE为他们工作。这包括PRIMARY KEY约束。上的文档CREATE TABLE具有更多详细信息：

非递延唯一性约束

当aUNIQUE或PRIMARY KEY约束不可延迟时，无论何时插入或修改一行，PostgreSQL都会立即检查其唯一性。SQL标准指出，唯一性应仅在语句末尾强制执行；否则，请参见参考资料。例如，当一个命令更新多个键值时，这会有所不同。为了获得符合标准的行为，请将约束声明为，DEFERRABLE但不要延迟（即INITIALLY IMMEDIATE）。请注意，这比立即进行唯一性检查要慢得多。