Tracking changes in values over time is a common requirement. For example, you may want to keep a history of product prices, employee salaries or student tutors.

The typical solution is to add start and end date columns to the table to record when a value is valid. For example, this stores product price changes:

The challenge is that each product must only have one active row at any given time. This means you must ensure start and end dates do not overlap.

The question is:

How?!

Oracle AI Database gives you two ways to do this:

Example of the constraints used to guarantee consecutive start-end date ranges

Guarantee consecutive start-end dates with foreign keys

You can guarantee consecutive dates with four standard constraints:

  • A primary key over ( entity id, start date ).
  • A check constraint ( start date < end date ).
  • A unique constraint over ( entity id, end date ).
  • A self-referencing foreign key from ( entity id, end date ) to ( entity id, start date ).

Add the last two constraints to the price history table to do this:

The unique constraint ensures that each product can only have one row with a null end date. This is the latest record. When you add a new price, set this to the start date of the next record.

By default, the foreign key blocks this change because there’s no matching start date yet. But the unique constraint means you can’t add the next record!

To overcome this the foreign key is deferrable. This means the database delays its validation until you commit. This allows you to set the current end date and insert the next start date in one transaction:

This solution works on any database that supports these constraints. But it has a couple of drawbacks:

  • The last row in the chain must always have a null end date.
  • Gaps between date ranges are disallowed.

These restrictions are often too strict.

For example, when an employee leaves a company or a product is discontinued, there is a final end date for their salary or price respectively.

Further, employees or students may take sabbaticals, and products may be temporarily removed from sale. This means gaps in the salaries, tutors or prices for these entities.

You can get around these issues by inserting dummy rows to bridge gaps or mark the end of a chain. But these fixes can lead to subtle errors. Queries need to filter out these dummy data. This is easy to overlook, leading to wrong results.

These requirements rule out the foreign key solution. Instead, you need complex code to detect and reject overlapping dates. Often, people rely on basic checks and hope for the best instead!

Starting in 23.26.1, Oracle AI Database offers a complete solution:

Assertions.

These enable you to define data rules using simple SQL.

To use assertions to prevent overlaps, first remove the foreign key enforcing consecutive date ranges:

Examples of valid and invalid time periods with respect to a reference period

Prevent overlapping start-end dates with assertions

Assertions enforce a Boolean rule. The database rejects any rows where this is false. To block overlapping periods, you need a query to flag invalid data, i.e. rows with overlapping ranges.

To find invalid rows, check:

  • Every pair of rows with the same product id,
  • Where the first row’s start date is before the second’s,
  • They overlap if the first end date is null or after the second’s start date.

You can turn this into an assertion like so:

The all-satisfy is new syntax that states:

  • For all product price rows,
  • There is no other row for the same product that starts after the first row but before the first row ends (or the first row has no end date).

With this assertion place, the database prevents any overlapping dates:

Summary

Overlapping dates in history tables are a common headache. Assertions let you define clear rules with simple SQL to prevent them. Oracle AI Database enforces the rule automatically.

Download Oracle AI Database Enterprise Edition and try assertions out today!