Databases
StackOverflow2013
Postsdbo
PODeclarative approach to constrain data ranges in table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeclarative approach to constrain data ranges in table
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>As I understand things with Oracle, <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_constraints.htm#ADFNS282" rel="nofollow">user-defined functions are not eligible for use in <code>CONSTRAINT</code> declarations</a> - 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.</p> <p>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.</p> <p>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.</p> <pre><code>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 &gt; 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' ); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMichael O'Neill
    1. USMichael O'Neill
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODeclarative approach to constrain data ranges in table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODeclarative approach to constrain data ranges in table
      1. USJon Heller
      1. VTFavorite
    3. VO
      singulars
      1. PODeclarative approach to constrain data ranges in table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt is difficult to *correctly* enforce this kind of "non-overlapping" constraint using triggers. You need some form of locking because you can't "see" changes that other sessions are making, and they might conflict with yours. See [here](http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:474221407101) and [here](http://jeffkemponoracle.com/2012/08/30/non-overlapping-dates-constraint/) for some discussion. Other approaches to consider might be: a) store only the "from" date and assume that each row is valid until the next "from" date, or, b) use a materialized view.
      singulars
      1. PODeclarative approach to constrain data ranges in table
      1. USBrian Camire
    2. CO@BrianCamire The first link to AskTom is a procedural solution. And, I guess I don't see yours or the Jeff Kemp article's point that this is a multi-user concurrency issue at heart. This has the same aspects of concurrency as a not null constraint. I am concerned only that a single transaction does not violate the constraint.
      singulars
      1. PODeclarative approach to constrain data ranges in table
      1. USMichael O'Neill
    3. COI disagree. A procedure solution is an option (although you still need locking), but the AskTom link discusses mostly trigger-based approaches. Concurrency is more of an issue than a not null constraint, because: a) a not null constrant can be enforced declaratively, and, b) a not null constraint applies only to one row at a time. To illustrate the concurreny issue, the solution you posted will not prevent two concurrent transactions from inserting identical (and therefore overlapping) rows at the same time.
      singulars
      1. PODeclarative approach to constrain data ranges in table
      1. USBrian Camire
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload