一对多关系中的标识符

Question

我有两个表，我们将它们称为 Foo 和 Bar，具有一对多关系，其中 Foo 是 的父级酒吧。 Foo 的主键是一个用序列自动生成的整数。

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

• Bar 的记录以编程方式进行 生成的用户输入不能 依赖于标识符。
• 多个进程正在生成 酒吧记录所以任何涉及 选择 Max() 生成 ID 将 提出竞争条件。

我提出了两种我不满意的可能解决方案：

• 将表格视为一个 与 a 的多对多关系 第三个表映射他们的记录 一起并申请 代码处理插入记录所以 记录之间的映射 已正确创建。 我不喜欢 这是因为它使得数据库设计 应用中的误导和错误 代码可能会导致无效数据。
• 给 Bar 两列：FooID 和 FooBarID 并生成一个值 FooBarID 通过选择 对于某些 FooID，max(FooBarID)+1，但是 如前所述，这会创建一个 竞赛条件。

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

Answer 1

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

除非我错过了一些东西，否则似乎没有理由它不起作用。

Answer 2

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

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');

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

Answer 3

根据您的描述，我假设您的数据库不支持自动增量标识符字段（MS SQL 支持，Oracle 有“序列”，即使不是更好，也同样好，我不记得 MySql 有）。

如果是的话，那么你只需要一个自增的FooId和一个自增的BarId，Bar还有一个FooId作为外键。

如果没有，那么你可以创建一个单行表进行分配，如下所示：

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

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

@BarId = BarIdAllocator = BarIdAllocator + 1

，那么您需要这样做

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

编辑：我最初错过了 Oracle 标签，所以 Bill 的解决方案就是必需的。 我将此答案作为一个示例，说明如果有人使用不支持身份或序列构造的数据库，如何执行此操作

Answer 4

根据 Ant P 和其他答案，我也不太明白为什么只为 bar 生成一个唯一的 ID 并删除 Foo 的 ID 是行不通的。 但假设您处于自动递增 ID 不可用的情况，那么有两种解决方案不涉及选择 max(barid)+1

1. 预生成唯一 ID 表并使用事务拉取下一个 ID从表中获取可用 ID 并将其删除（作为原子操作）。 这工作正常，但缺点是您必须保持表填充。

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