Without Overlaps Constraints

1. Functionality available with exclusion constraints
2. Without keyword for: PERIOD FOR BUSINESS_TIME (…)
3. Using a range: BUSINESS_TIME tsrange GENERATED ALWAYS AS (tsrange(start_ts, end_ts)) STORED

SQL supports temporal constraints that prevent rows with overlapping time ranges. These constraints are typically used to prevent double bookings in a reservation system.

Such a primary key or unique constraint uses the keywords without overlaps on the period that combines the begin and end of the time frame.0

CREATE TABLE t (
  id INTEGER NOT NULL,
  
  start_ts TIMESTAMP(6) NOT NULL,
  end_ts   TIMESTAMP(6) NOT NULL,
  PERIOD FOR BUSINESS_TIME (start_ts, end_ts),

  PRIMARY KEY (id, BUSINESS_TIME WITHOUT OVERLAPS)
)

Now consider these insert statements.

INSERT INTO t (id, start_ts, end_ts)
VALUES (1, TIMESTAMP'2018-05-16 19:12:50', TIMESTAMP'9999-12-31 23:59:59')

INSERT INTO t (id, start_ts, end_ts)
VALUES (1,  TIMESTAMP'2019-05-16 19:11:50', TIMESTAMP'2019-05-16 19:12:50')

Given the without overlaps constraint the second insert is rejected as the period in 2019 falls within the range of the first row that was already inserted (2018-9999).

Related

• Standard Features

  • T180, “System-versioned tables”

  • T181, “Application-time period tables”

  • T200, “Trigger DDL”

  • F521, “Assertions”

  • F671, “Subqueries in CHECK constraints”,

Normative References

Without overlaps constraints are defined in ISO/IEC 9075-2:2023 as part of the optional feature T181, “Application-time period tables”.