约束表中数据范围的声明性方法 [英] 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' ); 

推荐答案

等待下一个支持时间有效性，我仍然使用下一个方法:

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屋！