具有复合主键的序列 [英] Sequences with composite primary key

问题描述

使用PostgreSQL，如何在以下情况下管理序列（自动递增的整数ID）-

Using PostgreSQL, how do I manage a sequence (auto incremented Integer id) for the following scenario-

名为 businesses的表具有 id和一些

Table named 'businesses' which has 'id' and some more columns.

名为 users的表具有以下内容：

Table named 'users' which has:

1. 一个复合主键，由一个 business_id和一个 id组成，并重置了 id，当我现在插入行时，它将对每个 business_id进行计数。

1. a composite primary key, composed of a 'business_id', and an 'id', and have the 'id' reset it's counting for each 'business_id' when I insert now rows.

一个简单的列名称。

有点像每个企业有单独的顺序。

something like having a separate sequence per business.

当我运行以下查询时：

插入用户（business_id，名称）值（1，'a'）

insert into users (business_id, name) values (1, 'a')

插入用户（business_id，名称）值（2，'b'）

insert into users (business_id, name) values (2, 'b')

插入用户（business_id，name）值（1，'c'）

insert into users (business_id, name) values (1, 'c')

我想结束与以下用户表：

I want to end up with the following 'users' table:

business_id，id，名称

business_id, id, name

1、1 '

1，2，'c'

2，1，'b'

推荐答案

如果无法预测与一个业务相关的用户数（最大），则不能使用序列（即使可以，我也不建议以这种方式使用增量）。

You cannot use Sequences if you can´t predict how many (maximum) Users will be related to one businesses (and even if you can, I would not advise the use of increment that way).

如果您使用Postgres> = 9，则可以使用可以，通过将计数器放在主表的一列中来避免出现最大查询（或创建一个新表，该表包含一对business_id（PK），next_id，如果可以的话） t更改业务表）。

If you use Postgres >=9 you can use WITH to avoid the max query, by placing the counter as a column of the master table (or create a new table that holds a pair business_id(PK),next_id if you can´t change the Businesses table).

添加列next_id：

Add the column next_id:

ALTER TABLE Businesses ADD COLUMN next_id bigint;
//update current rows with default value
ALTER TABLE Businesses ALTER COLUMN next_id SET DEFAULT 0;
ALTER TABLE Businesses ALTER COLUMN next_id SET NOT NULL;

要插入到用户中，请使用以下单个SQL语句，该语句将返回新的ID。

To insert into Users, use the following single SQL statement, that returns the new id as a result.

With X as (
    Update Businesses
    set next_id=next_id+1 
    where id=:param_business_id
    Returning next_id)
Insert into Users (business_id,id,name)
Select :param_business_id, X.next_id ,:param_name
From X
Returning id

这将插入第一个id为 1，因为（第一个）返回子句返回更新后的值。
或者，如果您使用包含ID的单独表，只需将更新业务替换为更新MY_ID_TABLE。
请注意，此解决方案不是普通的SQL。

This will insert the first id as "1", because (the first) returning clause returns the updated value. Or if you use a separate table that holds the ids, just replace the "Update Businesses" to the "Update MY_ID_TABLE". Notice that this solution is not vanilla SQL.

这篇关于具有复合主键的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！