代码之家 › 专栏 › 技术社区 › Some Canuck

如何创建PGSQL中某个键的唯一自动递增修订号?

constraints sequence postgresql sql

Some Canuck · 技术社区 · 17 年前

假设我有以下表格。

PARENT: PARENT_ID serial, DESCRIPTION character varying(50)

CHILD: PARENT_ID integer, CHILD_ID integer, DESCRIPTION character varying(50)

我想看到的是,CHILD中的每一行都有一个CHILD\u ID,从1开始,递增1,每个PARENT\u ID都是唯一的。它类似于一个修订号。例如

PARENT_ID 1, CHILD_ID 1
PARENT_ID 1, CHILD_ID 2
PARENT_ID 1, CHILD_ID 3
PARENT_ID 2, CHILD_ID 1
PARENT_ID 3, CHILD_ID 1
PARENT_ID 3, CHILD_ID 2

是否有任何方法可以自动分配CHILD_ID值,例如序列或约束,只允许重用已删除的CHILD_ID?我能弄明白的唯一方法就是这个SQL的效果。

INSERT INTO child SELECT parent_id, MAX(child_id)+1, 'description' FROM child WHERE parent_id = :PARENT_ID GROUP BY parent_id

编辑: 标题很难看。如果你们中的任何一位得分较高的人能想出一个更准确的方法,请随意更改。

3 回复 | 直到 17 年前

Tometzky 17 年前

我建议使用:

CHILD: PARENT_ID integer, CHILD_ID serial, DESCRIPTION character varying(50)

当您需要获得期望的结果时:

您可以在客户端计算行数。

在即将发布的Postgresql 8.4中,您可以使用如下窗口功能:

$ create table child (parent_id integer, child_id serial);
NOTICE:  CREATE TABLE will create implicit sequence "child_child_id_seq" for serial column "child.child_id"
CREATE TABLE

$ insert into child (parent_id) values (1), (1), (1), (2), (3), (3);

$ select * from child;
 parent_id | child_id 
-----------+----------
         1 |        1
         1 |        2
         1 |        3
         2 |        4
         3 |        5
         3 |        6
(6 rows)

$ select parent_id, row_number() over (partition by parent_id order by child_id) from child;
 parent_id | row_number 
-----------+------
         1 |          1
         1 |          2
         1 |          3
         2 |          1
         3 |          1
         3 |          2
(6 rows)

它非常快速,易于实现,并且可以很好地扩展,因为不需要担心并发问题。

Tom H zenazn 17 年前

不过,这一插页并不是故事的全部。如果您真的希望这些数字是连续的,那么您还需要处理删除以缩小创建的间隙。

Michael Barker 17 年前

您可以在父表上使用递增的版本号,并将子id设置为该值并递增。您可能需要在单个事务中更新父行并插入子行。

BEGIN
-- Get and hold onto parent_id and version values.
SELECT PARENT_ID, VERSION FROM PARENT WHERE PARENT_ID = :PARENT_ID;
-- Use the values to insert into the child table
INSERT INTO CHILD (PARENT_ID, CHILD_ID) VALUES (:PARENT_ID, :VERSION);
-- Update the version using an optimistic lock.
UPDATE PARENT SET VERSION = VERSION + 1 WHERE PARENT_ID = :PARENT_ID AND 
                                              VERSION = :VERSION_ID
-- If no rows are updated rollback the transaction and try again.
END

不得不 重用ID然后您有2个选项,首先是您上面指定的解决方案,但在删除时会对删除后出现的所有值重新编号。另一个选项是使用某种函数,按顺序扫描子ID,将其与一组序列号进行比较,并在找不到第一个时返回值。这两种解决方案都比较复杂,速度也比较慢,因为您需要使用行锁来防止并发更新,并且插入或同时插入和删除都将受到O(n)惩罚。