Блог

Каков наилучший способ обеспечить уникальность ключа во временной таблице (СУБД Oracle)? Временная таблица — это таблица, в которой все исторические состояния записываются с указанием временного интервала.

Например, у нас есть такая ассоциация Ключ -> Значение…

 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 . Это период, в течение которого действительна карта Ключ-> Значение.
2. Для каждого ключа не может быть никаких перекрывающихся периодов. Период включает в себя момент START_PERIOD, но исключает точный момент END_PERIOD.
3. Принудительное применение ограничений может быть выполнено либо при вставке / обновлении строки, либо при фиксации. На самом деле мне все равно, главное, чтобы невозможно было зафиксировать неверные данные.

Мне сообщили, что наилучшей практикой для применения подобных ограничений является использование материализованных представлений вместо триггеров.

Пожалуйста, посоветуйте, как лучше всего этого добиться?

Знамя Оракула таково …

 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
 

То, что я пробовал до сих пор

Я думаю, что это решение близко, но на самом деле оно не работает, потому что требуется «при фиксации». Oracle, похоже, не способен создать материализованное представление такой сложности, которое обновляется при фиксации.

 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 ?

После некоторых усилий, экспериментов и рекомендаций из этого сообщения на форуме,

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

Почему это работает?

Почему это работает, но мое исходное опубликованное представление…

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

… не будет ли принято в качестве материализованного представления при фиксации? Ну, ответ заключается в том, что, по-видимому, существуют ограничения в сложности материализованных представлений при фиксации. Представления должны включать идентификаторы строк или ключи базовой таблицы и не должны превышать некоторый порог сложности.

Существует метод, который я видел описанным для SQL Server (см. Эту статью и найдите «Таблица истории Кузнецова»), который добавляет третий столбец времени, previous_end_period который вы можете использовать для установки внешнего ключа в самой таблице, чтобы обеспечить ограничение, что интервалы не могут перекрываться. Я не знаю, можно ли это адаптировать к Oracle.

Отличное решение, Шон!

Но я бы добавил комментарии к вашим объектам из-за сложности … что-то вроде:

 COMMENT ON COLUMN TEMPORAL_VALUE.KEY IS 'Each key may have at most only one value for any instant in time';
COMMENT ON COLUMN TEMPORAL_VALUE.START_PERIOD IS 'The period described includes the START_PERIOD date/time';
COMMENT ON COLUMN TEMPORAL_VALUE.END_PERIOD IS 'The period described does not included the END_PERIOD date/time. A null end period means until forever';

COMMENT ON COLUMN TEMPORAL_VALUE IS 'Integrity is enforced by the MATERIALIZED VIEW OVERLAPPING_VALUES';

COMMENT ON MATERIALIZED VIEW OVERLAPPING_VALUES IS 'Used to enforce the rule - each key may have at most only one value for any instant in time. This is an [on commit] mv, that holds any temporal values that overlaps another (for the same key), but the CHECK(0=1) constraint will raise an exception if any rows are found, stopping any commit that would break integrity';
 

Лично мне нравится добавлять ко всем материализованным именам представлений префикс MV_, а к представлениям — V_

Интересно, что вы не разрешаете START_PERIOD иметь значение null. Большинство реализаций позволили бы нулевому началу и ненулевому концу указывать период, предшествующий всему, а нулевые значения для обоих бейтов указывают постоянное значение для ключа.