MySQL 基于多种因素(位置、日期和时间)触发/检查 INSERT

Question

我有一个适用于各个仓库的排类计划表。我正在尝试在数据库级别添加多项检查。如果我使用复合键(通过删除 shift_id)来匹配员工、位置、日期时间，它可以工作，但它不能解决轮类重叠问题(例如，如果我插入与 shift_id 1 完全相同的数据，但不是 09:00 开始时间是 09:01 将被接受为有效类次)。

在下面的示例中，shift_id 2 的 INSERT 应该失败，因为相同的 staff_id 1 的轮类时间重叠/strong> 在同一日期 13:00 之后，同一员工的下一个可用轮类(在同一日期和同一地点或不同的地点)应该可用。对于 shift_id 3 应该是另一个失败，因为 staff_id 1 已经安排在完全相同的日期和相同的时间到另一个位置(location_id 1)。但是，*shift_id 4 * 应该被接受，因为对于相同的员工 ID，轮类开始/结束不重叠。

我试图添加表约束来比较 shift_start 和 shift_end 但没有成功:

 ADD CONSTRAINT NoDoubleShifts CHECK(
    NOT EXISTS (
            SELECT 1
            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 抛出错误(我猜测约束格式不正确“找到了一条语句，但在 CHECK 附近没有分隔符”)。

第二个尝试的解决方案是在不同的组合中添加一个 BEFORE INSERT 触发器，但也没有用。正在计算时间，但阻止我在该时间段内添加任何其他员工(具有不同的 ID)。

begin
  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';
  end if;
end;

任何帮助和建设性意见或任何为我指明正确方向的人都将不胜感激。

提前致谢..

Answer 1

就像 Nick 在他的评论中建议的那样，解决方案非常简单。因此最终触发器看起来像:

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start
             and staff_id = new.staff_id) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end

再次感谢 Nick....