Hi Gary,
I've actually just done the same thing - but for renting property. I've implemented the constraint as a trigger (Before
insert/update,for each row), that first checks if the start_date is < end_date, and then performs a select on the
bookingstable using the OVERLAPS function. If there are more than 0 records returned, an exception is raised.
I've included the code below. You shouldn't need too many changes to adapt it to your needs!
/* This trigger function is responsible for ensuring temporal integrity
within the calendar_entries table (And it's children). It ensures that
only entries with no overlapping entries. */
BEGIN
/* First, check that the start_date > end_date */
IF NEW.start_date > NEW.end_date THEN
RAISE EXCEPTION 'ERROR: start_date must not be greater than end_date';
END IF;
IF EXISTS (
SELECT 1
FROM calendar_entries
WHERE ((start_date,end_date) OVERLAPS (NEW.start_date, NEW.end_date))
AND property_id = NEW.property_id LIMIT 1
)
THEN
RAISE EXCEPTION 'cannot add booking - overlapping calendar entries detected';
END IF;
RETURN NEW;
END;
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private
information. If you have received it in error, please notify the sender immediately and delete the original. Any other
useof the email by you is prohibited.
