postgresql - 表相互引用解决方法

Question

我有两个表（简化）：

+-------------+--------------+----------------------------+
|                        article                          |
+-------------+--------------+----------------------------+
| id          | int          | PRIMARY KEY AUTO_INCREMENT |
| author      | int          | FK (users)                 |
| created_at  | date         |                            |
| revision    | int          | FK (article_revision)      |                  
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                   article_revision                      |
+-------------+--------------+----------------------------+
| id          | int          | PRIMARY KEY AUTO_INCREMENT |
| title       | text         |                            |
| content     | text         |                            |
| article_id  | int          | FOREIGN KEY (article)      |
+-------------+--------------+----------------------------+

它们代表新闻提要文章。每篇文章都可以有多个修订（更改）。我需要以某种方式从使用的文章表中指定一个当前修订版（可能有可以使用的待定修订版，当时只有一个）。但这将是循环引用。这种情况有什么解决方法吗？

标签： postgresqldatabase-design

Answer 1

没有什么可以阻止您添加循环外键引用。

要处理它们，您只需要确保在每个语句或事务结束时，所有约束都得到满足：

• 如果您使用普通NOT DEFERRABLE约束，则必须在单个语句中修改两个表：

  WITH current_revision AS (
     INSERT INTO article_revision VALUES (...) RETURNING id
  )
  UPDATE article
  FROM current_revision
  SET revision = current_revision.id
  WHERE ...;
  

• 使用DEFERRABLE外键，检查被推迟到事务结束：

  BEGIN;
  INSERT INTO article_revision VALUES (...) RETURNING id;
  UPDATE article SET revision = ... WHERE ...;
  COMMIT;