database - 为每个外键单独自增

Question

我有两个表:

 CREATE TABLE "user"
    (
      username character varying(35) NOT NULL,
      CONSTRAINT user_pk PRIMARY KEY (username)
    )
    CREATE TABLE item
    (
      id serial NOT NULL,
      username character varying(35),
      user_item_number integer,
      item_value character varying(35),
      CONSTRAINT item_pk PRIMARY KEY (id),
      CONSTRAINT item_fk FOREIGN KEY (username)
          REFERENCES "user" (username) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT unique_item_username UNIQUE (username, user_item_number)
    )

我想为每个 username 分别自动增加 user_item_number。下图显示了示例。对于每个 username:(user1, user2) user_item_number 从 1 开始并递增 1。

我想我应该在插入之前使用一些触发器，它为插入的 username 获取 user_item_number 的最大值并递增它。但我不知道如何编写该触发器。

我也不知道如何考虑并发访问(并发插入多个具有相同 username 值的行)。我不想在插入具有相同 username 和 user_item_number 的两行时出现约束违反错误，我希望触发器捕获该错误并再次递增 user_item_number 值。

有什么想法吗？

Answer 1

生成和维护这样的无缝序列真的很难。

获得相同结果的更好方法是使用窗口函数动态生成此类序列。像这样的东西:

SELECT id, username, row_number() OVER (PARTITION BY username ORDER BY id) as user_item_number, item_value
from item_table;

它会给你想要的结果并且不会导致任何并发问题。它还将始终保持序列无缝。