一对多关系中的标识符

I have two tables, we'll call them Foo and Bar, with a one to many relationship where Foo is the parent of Bar. Foo's primary key is an integer automatically generated with a sequence.

我有两张桌子,我们称之为Foo和Bar,有一对多的关系,其中Foo是Bar的父母。 Foo的主键是使用序列自动生成的整数。

Since Bar is fully dependent on Foo how would I setup the primary key of Bar given the following constraints:

由于Bar完全依赖于Foo,如何在给定以下约束的情况下设置Bar的主键:

Records for Bar are programatically generated so user input can not be relied upon for an identifier.

以编程方式生成Bar的记录,因此不能依赖用户输入标识符。

Multiple processes are generating Bar records so anything involving a Select Max() to generate an ID would present a race condition.

多个进程正在生成Bar记录,因此任何涉及Select Max()生成ID的内容都会出现竞争条件。

I have come up with two possible solutions that I am not happy with:

我想出了两个我不满意的可能解决方案:

Treat the tables as if they are a many to many relationship with a third table that maps their records together and have the application code handle inserting records so that the mapping between the records is created correctly. I don't like this as it makes the database design misleading and errors in application code could result in invalid data.

将表视为与第三个表的多对多关系,将第三个表映射到一起并让应用程序代码处理插入记录,以便正确创建记录之间的映射。我不喜欢这样,因为它使数据库设计误导,应用程序代码中的错误可能导致无效数据。

Give Bar two colunms: FooID and FooBarID and generate a value for FooBarID by selecting the max(FooBarID)+1 for some FooID, but as previously stated this creates a race condition.

给Bar两个colunms:FooID和FooBarID,并通过为某些FooID选择max(FooBarID)+1来为FooBarID生成一个值,但如前所述,这会产生竞争条件。

I appreciate any ideas for an alternative table layout.

我很欣赏任何替代表格布局的想法。

4 个解决方案

#1

Give Bar an automatic primary key the same as with Foo. Add a foreign key FooID column to Bar.

为Bar提供与Foo相同的自动主键。将外键FooID列添加到Bar。

Unless I'm missing something, there doesn't seem to be a reason why it wouldn't work.

除非我遗漏了某些东西,否则它似乎没有理由不起作用。

#2

Unless I'm missing something in your description, this sounds like an ordinary case. The usual solution is something like this:

除非我在你的描述中遗漏了一些东西,否则这听起来像一个普通的案例。通常的解决方案是这样的:

INSERT INTO Foo (foo_id, othercolumn)
  VALUES ( FooSeq.NextVal(), 'yadda yadda');

INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'blah blah');
INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'bling bling');
INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'baz baz');

The CURRVAL() function of a sequence only returns the most recent value generated by that sequence during your current session. Other concurrent use of that sequence doesn't affect what CURRVAL() returns in your session.

序列的CURRVAL()函数仅返回当前会话期间该序列生成的最新值。其他并发使用该序列不会影响CURRVAL()在会话中返回的内容。

#3

from your description i'm assuming that your database does not support auto-increment identifier fields (MS SQL does, Oracle has 'sequences' which are just as good if not better, I don't remember MySql has).

从你的描述中我假设你的数据库不支持自动增量标识符字段(MS SQL确实如此,Oracle有'序列',如果不是更好,那就好了,我不记得MySql了)。

If it does, then all you need is an auto-increment FooId and an auto-increment BarId, and Bar also has a FooId as a foreign key

如果是这样,那么你需要的只是一个自动增量FooId和一个自动增量BarId,而Bar也有一个FooId作为外键

If it does not, then you can create a single-row table for allocation as follows:

如果没有,那么您可以创建一个单行表进行分配,如下所示:

create table SystemCounter 
( 
    SystemCounterId int identity not null, 
    BarIdAllocator int 
)
--initialize SystemCounter to have one record with SystemCounterId = 1
--and BarIdAllocator = 0
insert into SystemCounter values (1,0)
--id allocator procedure
create procedure GetNextBarId ( @BarId int output ) AS
    SET NOCOUNT ON
    begin tran
        update SystemCounter set 
            @BarId = BarIdAllocator = BarIdAllocator + 1
        where SystemCounterId = 1
    commit
GO

note that if your database does not support the syntax

请注意,如果您的数据库不支持语法

@BarId = BarIdAllocator = BarIdAllocator + 1

then you'll need to do it this way instead

那么你需要这样做

begin tran
    update SystemCounter set 
        BarIdAllocator = BarIdAllocator + 1
    where SystemCounterId = 1
    select 
        @BarId = BarIdAllocator
    from SystemCounter
    where SystemCounterId = 1
commit

EDIT: I missed the Oracle tag originally, so Bill's solution is all that is necessary. Am leaving this answer as an example of how to do it in case someone is using a database that does not support identity or sequence constructs

编辑:我最初错过了Oracle标签,所以Bill的解决方案就是必要的。如果有人使用不支持身份或序列构造的数据库,请留下这个答案作为如何执行此操作的示例

#4

I can't quite see either, as per Ant P's and other answers, why which just generating a unique ID for bar and dropping Foo's ID in won't work. But supposing you're in a situation where auto-incrementing IDs are not available then there's two solutions that do not involve selecting max(barid)+1

根据Ant P和其他答案,我不能完全看到为什么只为bar生成一个唯一的ID并丢弃Foo的ID将无效。但假设您处于自动递增ID不可用的情况,那么有两个解决方案不涉及选择max(barid)+1

Pre-generate a table of unique IDs and use a transaction to pull the next available ID from the table and delete it (as an atomic operation). This works fine but has the disadvantage that you have to keep the table populated.

预先生成一个唯一ID表,并使用事务从表中提取下一个可用ID并将其删除(作为原子操作)。这样可以正常工作,但缺点是必须保持表的填充状态。
Generate a UUID as the primary key. This isn't generally a good option as UUIDs are inefficient for this use, but it does have the advantage that no additional infrastructure tables are needed. UUID generators are widely available and some databases have them built in.

生成UUID作为主键。这通常不是一个好的选择,因为UUID对于此用途来说效率低,但它确实具有不需要额外的基础结构表的优点。 UUID生成器广泛可用,一些数据库内置它们。

#1