java - 如何将子记录的插入限制为N条记录？

Question

我正在使用 Oracle 数据库。我有两个表，假设表 A 和表 B。它们之间有 1:N 关系。我需要将“N”限制为特定数字，例如 10。对于每个插入的记录，我还需要第 n 个数字。

数据是通过部署在多个节点上的Java应用程序插入的。

表 B 看起来像(ForeignId 将是表 A 的主键):

+----+----------+----------+-----------+
| Id |   Data   | UniqueId | ForeignId |
+----+----------+----------+-----------+
|  1 | Somedata |        1 |         1 |
|  2 | Somedata |        2 |         1 |
|    |          |          |           |
+----+----------+----------+-----------+

我想出的解决方案:

• 我制作了一个具有唯一性的复合键(UniqueId，ForeignId)约束。
• 开启自动提交。
• UniqueId 的值是通过 select 语句计算出来的，而数据已插入。
• 如果遇到唯一约束异常，我们会重试。
• 如果 UniqueId 的值 > 10，我们将删除该行并回滚。

到目前为止，这个解决方案已经奏效，但我觉得必须有更好的方法来实现这一目标。

我是否过度设计了？

Answer 1

I cannot have the value 11. It needs to be 1-10

您可以使用检查约束来防止添加大于 10 的值；作为大纲(例如，您可能需要限制为整数；并使用更有意义的名称...):

create table a (
  id number,
  constraint a_pk primary key (id)
);

create table b (
  id number,
  data varchar2(10),
  foreign_id number not null,
  unique_id number not null,
  constraint b_pk primary key (id),
  constraint b_fk foreign key (foreign_id) references a (id),
  constraint b_uk unique (foreign_id, unique_id),
  constraint b_ch check (unique_id between 1 and 10)
);

insert into a (id) values (101);
insert into a (id) values (102);

然后您最多可以插入 10 个:

insert into b (id, data, foreign_id, unique_id)
select 1, 'SomeData', 101, nvl(max(unique_id), 0) + 1
from b
where foreign_id = 101;

insert into b (id, data, foreign_id, unique_id)
select 2, 'SomeData', 101, nvl(max(unique_id), 0) + 1
from b
where foreign_id = 101;

...

insert into b (id, data, foreign_id, unique_id)
select 10, 'SomeData', 101, nvl(max(unique_id), 0) + 1
from b
where foreign_id = 101;

但尝试插入第 11 个值失败:

insert into b (id, data, foreign_id, unique_id)
select 11, 'SomeData', 101, nvl(max(unique_id), 0) + 1
from b
where foreign_id = 101;

ORA-02290: check constraint (MY_SCHEMA.B_CH) violated

尝试插入重复项仍然会失败:

insert into b (id, data, foreign_id, unique_id)
values (11, 'SomeData', 101, 1);

ORA-00001: unique constraint (MYSCHEMA.B_UK) violated

但是不同的外键有相同的“唯一”ID:

insert into b (id, data, foreign_id, unique_id)
select 11, 'SomeData', 102, nvl(max(unique_id), 0) + 1
from b
where foreign_id = 102;

select * from b;

        ID DATA       FOREIGN_ID  UNIQUE_ID
---------- ---------- ---------- ----------
         1 SomeData          101          1
         2 SomeData          101          2
         3 SomeData          101          3
         4 SomeData          101          4
         5 SomeData          101          5
         6 SomeData          101          6
         7 SomeData          101          7
         8 SomeData          101          8
         9 SomeData          101          9
        10 SomeData          101         10
        11 SomeData          102          1

我为每个外键获取下一个唯一 ID 的方式意味着，如果您删除一行，则无法重新使用其 ID，除非您删除的行恰好是最高值。如果您想填补空白，您可以使插入更加复杂，但您现在还没有显示如何计算，因此您可能不想这样做。又是一个粗略的方法:

delete from b where id = 3;

insert into b (id, data, foreign_id, unique_id)
values (12, 'SomeData', 101,
  (
    select min(n) from (select level as n from dual connect by level <= 10) x
    where not exists (
      select null from b where b.foreign_id = 101 and b.unique_id = x.n
    )
  )
);

如果没有间隙，标量子查询将获取null，因此插入在非空检查时失败。

db<>fiddle

<小时/>

如果您想要最多 10 个值，但它们可能不限于 1-10，您可以使用物化 View 来保留当前计数，并针对不超过 10 进行检查约束。不过，这似乎就是您想要的。