mysql constarint interval intersection

Question

I have mysql table

CREATE TABLE `range` (
  `id` int(11) NOT NULL,
  `object_id` int NOT NULL,
  `datetime_from` datetime NOT NULL,
  `datetime_to` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please help to provide on mysql level constraint implementation: no time interval intersection for same object_id.

MySQL does not have check constraints implemented. Even if they were, you might not be able to use constraints for what you have in mind. Most likely, you'll have to use a trigger here to accomplish what you want. — Tim Biegeleisen, Jun 03 '18 at 12:22
Yeah this requires a `BEFORE INSERT` trigger to check neither the new `datetime_from` nor `datetime_to` falls between an existing pair, then sets sqlstate to abort the insert as in https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342 — Michael Berkowski, Jun 03 '18 at 12:26

score 0 · Accepted Answer · answered Jun 03 '18 at 12:37

A trigger is fine, but by way of demonstrating that no trigger is required, consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (
  id SERIAL,
  dt_from DATE NOT NULL,
  dt_to DATE NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO my_table (dt_from,dt_to)
VALUES
('2018-05-31','2018-06-03');

-- Attempt 1: conflicting dates

SET @dt_from = '2018-05-28';
SET @dt_to = '2018-06-01';

INSERT INTO my_table (dt_from,dt_to) 
SELECT @dt_from
     , @dt_to
  FROM (SELECT 1) x
  LEFT 
  JOIN my_table y 
    ON y.dt_from < @dt_to 
   AND y.dt_to > @dt_from 
 WHERE y.id IS NULL;

-- Attempt 2: Non-conflicting dates

SET @dt_from = '2018-06-04'; 
SET @dt_to = '2018-06-06'; 

INSERT INTO my_table (dt_from,dt_to) 
SELECT @dt_from
     , @dt_to
  FROM (SELECT 1) x
  LEFT 
  JOIN my_table y 
    ON y.dt_from < @dt_to 
   AND y.dt_to > @dt_from 
 WHERE y.id IS NULL;

 SELECT * FROM my_table;
+----+------------+------------+
| id | dt_from    | dt_to      |
+----+------------+------------+
|  1 | 2018-05-31 | 2018-06-03 |
|  2 | 2018-06-04 | 2018-06-06 |
+----+------------+------------+

See. Conflicting dates are ignored.

mysql constarint interval intersection

1 Answers1