限制表中数据范围的声明方法 [英] Declarative approach to constrain data ranges in table

问题描述

我想学习一个声明性的方法来解决数据约束问题，我不时地和独家日期范围有关。

I would like to learn a declarative approach for a data constraint issue I have had from time to time related to exclusive date ranges.

下面是一个简化的例子。我有这些物品和价格。我希望价格的生效日期范围是相互排斥的，没有重叠。

Below is a simplified example. I have items and prices on those items. I want the effective date range of the prices to be mutually exclusive with no overlap.

正如我所理解的一样，Oracle，用户定义的函数不适用于 CONSTRAINT 声明 - 我甚至不能想象如果允许的话，表现如何差。所以我需要使用触发器的程序方法。典型的触发源也包含在下面。

As I understand things with Oracle, user-defined functions are not eligible for use in CONSTRAINT declarations - and I can't even imagine how poorly it would perform if it were allowed. So I require a procedural approach using triggers. Typical trigger source is also included below.

我不感兴趣在触发器中学习更好的程序逻辑（这只是一个简单的示范示例）。我有兴趣学习一个比较受欢迎的数据约束问题的一个更具声明性的解决方案（可能是其他人）。

I am not interested in learning better procedural logic for use within the trigger (it is just a simple demonstrative example). I am interested in learning a more declarative solution to a relatively popular data constraint issue I (and likely others) face.

我想消除基于触发器的解决方案，作为一种做法。不过没有触发器，我似乎找不到我的出路。

I want to eliminate trigger-based solutions whenever I can, as a practice. However I can't seem to find my way out of this requirement without a trigger.

create table item ( title varchar2(32) primary key );
create table price ( 
   item           varchar2(32) not null references item (title), 
   price          number(9,2), 
   effective_from date not null, 
   effective_to   date not null, 
   constraint price_from_to_ck check (effective_to > effective_from ));

[REDACTED]
*(A combination of row and statement level triggers inteneded to prevent logical chronological overlap)

insert into item values ('LETTUCE');
insert into item values ('WHISKY');

insert into price values ( 'LETTUCE', 1.05, date '2013-01-01', date '2013-03-31' );
insert into price values ( 'LETTUCE', 1.08, date '2013-04-01', date '2013-06-30' ); 
insert into price values ( 'WHISKY', 33.99, date '2013-01-01', date '2013-05-31' );
insert into price values ( 'WHISKY', 31.15, date '2013-06-01', date '2013-07-31' ); 

-- should fail
insert into price values ( 'WHISKY', 30.55, date '2013-05-15', date '2013-06-05' ); 

推荐答案

在等待下一个Oracle 12c版本时， a href =http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967 =nofollow>时间有效性，我仍然使用下一个方法：

While waiting for next Oracle 12c version, which supports Temporal Validity, I still use next approach:

create table item ( title varchar2(32) primary key );
create table price ( 
   price_id          number primary key,
   item              varchar2(32) not null references item (title), 
   price             number(9,2), 
   effective_from    date not null, 
   effective_to      date not null, 
   effective_prev_to date,
   constraint price_from_to_ck check ( effective_to > effective_from ),
   constraint price_to_prev_ck check ( effective_from = effective_prev_to + 1 ),
   constraint price_from_uq unique ( item, effective_to ),
   constraint price_dates_chain_fk foreign key ( item, effective_prev_to ) references price ( item, effective_to ) );

insert into item values ('LETTUCE');
insert into item values ('WHISKY');

insert into price values ( 1, 'LETTUCE', 1.05, date '2013-01-01', date '2013-03-31', null );
insert into price values ( 2, 'LETTUCE', 1.08, date '2013-04-01', date '2013-06-30', date '2013-03-31' ); 
insert into price values ( 3, 'WHISKY', 33.99, date '2013-01-01', date '2013-05-31', null );
insert into price values ( 4, 'WHISKY', 31.15, date '2013-06-01', date '2013-07-31', date '2013-05-31' ); 

让我们尝试：

insert into price values ( 5, 'WHISKY', 30.55, date '2013-05-15', date '2013-06-05', date '2013-05-14' ); 

ORA-02291: integrity constraint (USER_4_E7DF1.PRICE_DATES_CHAIN_FK) violated - parent key not found : insert into price values ( 'WHISKY', 30.55, date '2013-05-15', date '2013-06-05', date '2013-05-14' )

但现在更新和删除日期链的中间是屁股的痛苦。它需要在一个语句中更改 MERGE 之前和之后的行。这就是为什么我添加了 price_id 列，因为您无法更新 MERGE 中的键 - 所以，你需要另一个键，而不是（item，effective _％）。

But now updating and deleting dates in the middle of the chain is pain in the ass. It needs to change preceding and following rows in one statement with MERGE. That's why I've added price_id column, because you can't update a key in MERGE -- so, you need another key instead of (item, effective_%).

这篇关于限制表中数据范围的声明方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！