Dealing with Overlapping Date Ranges in MySQL

Inserting new date ranges into an existing table can be tricky, especially when trying to prevent overlaps with existing entries. In this context, we want to avoid inserting ranges that overlap with those in the database for a specific account identifier (acc_id).

While MySQL offers the option to validate dates using SQL CHECK constraints, this feature is unfortunately not supported by MySQL. PostgreSQL, on the other hand, supports CHECK constraints, but switching database engines may not be feasible for all situations.

As an alternative, we can implement the validation in the application logic. One approach involves using a SELECT COUNT(id) ... statement to check for overlaps before attempting an INSERT:

SELECT COUNT(id)
FROM avail
WHERE acc_id = '175'
AND (start_date BETWEEN '2015-05-30' AND '2015-06-04')
OR (end_date BETWEEN '2015-05-30' AND '2015-06-04')
OR ('2015-05-30' BETWEEN start_date AND end_date)
OR ('2015-06-04' BETWEEN start_date AND end_date);

If the returned count is greater than 0, the insert is aborted due to an overlap.

Another method involves using a trigger in MySQL. A trigger can be programmed to check for overlaps before the INSERT/UPDATE occurs and throw an error if any are found. Triggers, however, require an up-to-date MySQL version.

Ultimately, the choice of approach depends on the specific requirements of your application and the feasibility of implementing it in one of the supported ways.