I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works but it doesn't solve the shift overlapping problem (e.g if I insert exact same data as shift_id 1 but instead of 09:00 start time is 09:01 will accept as valid shift).
In below example, the INSERT should fail for shift_id 2 because, the shift time is overlapped for same staff_id 1 on same date The next available shift for same staff (on same date and same location or a different one) should be available after 13:00. For shift_id 3 should be another fail because staff_id 1 is already scheduled for exact same date and same hours to another location (location_id 1).
I was trying to add table constraint to compare shift_start and shift_end but unsuccessfully:
ADD CONSTRAINT NoDoubleShifts CHECK(
NOT EXISTS (
FROM Shift S1, Shift S2
WHERE s1.shift_date + s1.shift_start < s2.shift_date + s2shift_start
AND s1.shift_date + s1.shift_end > s2.shift_Date + s2shift_start)
MySQl throw errors (I'm guessing that the constraint is not well formatted "A statement was found, but no delimiter betwen it near CHECK").
The second trying solution was to add a trigger BEFORE INSERT in different combinations but didn't work either. Is calculating the time but is preventing me to add any other staff (with different id) during that time slot.
if exists (select * from shift
where shift_start <= new.shift_end
and shift_end >= new.shift_start ) then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
Any help and constructive comments or anyone to pint me in right direction will be highly appreciated.
Thanks in advance..