sql - 如何在临时表中强制执行 key 唯一性？

Question

在时态表 (Oracle DBMS) 中强制键唯一性的最佳方法是什么。时态表是一个时间跨度记录所有历史状态的表。

例如，我们有一个这样的 Key --> Value 关联 ...

create table TEMPORAL_VALUES
    (KEY1               varchar2(99) not null,
     VALUE1             varchar2(99),
     START_PERIOD       date not null,
     END_PERIOD         date not null);

对于表的时间性质，有两个约束要强制执行，即:

1. 对于每条记录，我们必须有 END_PERIOD > START_PERIOD。这是 Key->Value 映射有效的时间段。

2. 对于每个Key，不能有任何重叠的句点。该期间包括 START_PERIOD 的时刻，但不包括 END_PERIOD 的确切时刻。

3. 可以在行插入/更新或提交时执行约束。我真的不在乎，只要不可能提交无效数据即可。

我去过informed that the best practice强制执行这样的约束是使用物化 View 而不是触发器。

请告知实现此目标的最佳方法是什么？

Oracle 的旗帜是......

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

---

到目前为止我尝试了什么

我认为这个解决方案很接近，但它并没有真正起作用，因为需要“提交时”。 Oracle 似乎无法创建这种在提交时刷新的复杂性的物化 View 。

create materialized view OVERLAPPING_VALUES
  nologging cache build immediate 
  refresh complete on demand
  as select 'Wrong!'
      from
        (
        select KEY1, END_PERIOD,
               lead( START_PERIOD, 1) over (partition by KEY1 order by START_PERIOD) as NEXT_START
          from TEMPORAL_VALUES
        )
      where NEXT_START < END_PERIOD;
alter table OVERLAPPING_VALUES add CHECK( 0 = 1 );

我做错了什么？我如何在提交时完成这项工作以防止 TEMPORAL_VALUES 中出现无效行？

Answer 1

经过一些努力、实验和来自 this forum post 的指导,

drop table TEMPORAL_VALUE;
create table TEMPORAL_VALUE
    (KEY1                          varchar2(99) not null,
    VALUE1                         varchar2(99),
    START_PERIOD                   date not null,
    END_PERIOD                     date
    )
/
alter table TEMPORAL_VALUE add
  constraint CHECK_PERIOD check ( END_PERIOD is null or END_PERIOD > START_PERIOD)
/  
alter table TEMPORAL_VALUE add
  constraint PK_TEMPORAL_VALUE primary key (KEY1, START_PERIOD)
/
alter table TEMPORAL_VALUE add
  constraint UNIQUE_END_PERIOD unique (KEY1, END_PERIOD)
/
create materialized view log on TEMPORAL_VALUE with rowid;

drop materialized view OVERLAPPING_VALUES;

create materialized view OVERLAPPING_VALUES
  build immediate refresh fast on commit as
  select a.rowid a_rowid, b.rowid b_rowid
    from TEMPORAL_VALUE a, TEMPORAL_VALUE b
    where a.KEY1 = b.KEY1
      and a.rowid <> b.rowid
      and a.START_PERIOD <= b.START_PERIOD
      and (a.END_PERIOD is null or (a.END_PERIOD >  b.START_PERIOD));

alter table OVERLAPPING_VALUES add CHECK( 0 = 1 );

为什么会这样？

为什么这行得通，但是我原来发布的 View ...

select KEY1, END_PERIOD,
               lead( START_PERIOD, 1) over (partition by KEY1 order by START_PERIOD) as NEXT_START
          from TEMPORAL_VALUES

...将不会被接受为 On-Commit 物化 View ？好吧，答案是提交物化 View 的复杂性似乎存在限制。 View 必须包含基础表的行 ID 或键，并且不能超过某个复杂度阈值。