问题描述
使用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:
-
一个复合主键，由一个 business_id和一个 id组成，并重置了 id，当我现在插入行时，它将对每个 business_id进行计数。
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.
这篇关于具有复合主键的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！