sql - 如何自动递增作用域为另一个表的非主 ID 列？

Question

假设我们正在构建 GitHub，我们有两个表:repos 和 issues。每个 GitHub 存储库都有一个问题集合，因此 issues 表有一个外键 repo_id。

现在，当您浏览 GitHub 存储库的问题时，您不希望看到内部 id。相反，您需要类似 number 的东西，它仅针对该存储库从 1..n 递增。您希望新存储库中的第一个问题编号为 1，而不是 GitHub 上问题的下一个 id 编号。

当然，您需要一种递增的方法，并且您希望确保 number 在存储库范围内是唯一的。因此，您特别想避免任何可能生成两次相同数字的竞争条件。

处理此问题最直接的方法是什么？触发器？完全不同的东西？

我正在使用 PostgreSQL，但在可能的情况下更喜欢普通 SQL 的方法，例如触发器。如果有更简单的 Postgres 方法，那也会很有用。

任何展示您的方法的代码都将非常有用。谢谢!

Answer 1

我不认为有一种方法可以在没有竞争条件的情况下做到这一点。这应该最大限度地减少竞争条件，但不会消除它们。在特定的数据库架构中可能有更好的方法。假设“REPOSITORY_ID”由您的应用程序代码提供:

insert into issues (repo_id,line_id) values (
    REPOSITORY_ID,
    coalesce((select max(line_id)+1 from issues where repo_id=REPOSITORY_ID),0)
);

这会拉取当前最高的 line_id 并在插入时递增它。如果没有记录，则默认为 0。如果两个插入恰好同时命中，则竞争的可能性很小，但似乎不太可能。如果强制执行唯一性，则可以在插入时检查错误并在失败时重试。