MariaDB Day Brussels 02.02.2020 – Introducing speakers – Nikita Malyavin on 5 Application-time periods in MariaDB – new syntax and table constraints: from PORTIONs to FOREIGN KEYs

We continue presenting our MariaDB Day Speakers. Please remember to register for the event at the Meetup page.

Application-time periods is another feature of SQL-2011. It’s main purpose is to introduce a per-table PERIOD entity, which generally is a pair of temporal fields: [period start, period end), and to set up a new set of syntax and constraints.

Note: the standard forbids these fields to be of type TIME, and to have more than one period per table.

The eminent part of application-time periods is a UNIQUE semantic extension — it adds a possibility to specify a period there, with adding WITHOUT OVERLAPS after it. This changes the semantics from ordinary key fields concatenation to checking that for each key none of two records have overlapping periods.

The even more eminent part is a new referential integrity check: the feature allows to specify the period at the end of FOREIGN KEY fields list, to ensure that all the referencing periods are covered by the referenced ones.

The talk is going to cover following topics:
* basic deleting and updating of PORTIONs of periods
* WITHOUT OVERLAPS constraint
* referential integrity of periods
* performance issues
* data structures to resolve the referential integrity checks efficiently