By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE prices ( id INT AUTO_INCREMENT PRIMARY KEY,
productID INT,
startDate DATE,
endDate DATE,
price DECIMAL(10, 2),
CHECK (startDate <= EndDate));
CREATE TRIGGER check_for_overlapping
BEFORE INSERT
ON prices
FOR EACH ROW
BEGIN
IF EXISTS ( SELECT NULL
FROM prices
WHERE prices.productID = NEW.productID
AND prices.startDate <= NEW.EndDate
AND NEW.startDate <= prices.EndDate ) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Entered data overlaps with existing data';
END IF;
END;
-- valid data
INSERT INTO prices (productID, startDate, endDate, price)
VALUES
(1, '2020-01-01', '2020-02-01', 120),
(1, '2020-02-02', '2020-03-02', 121),
(1, '2020-03-03', '2020-04-03', 122);
-- valid data
INSERT INTO prices (productID, startDate, endDate, price)
VALUES
(2, '2020-01-01', '2020-02-01', 123),
(2, '2020-02-02', '2020-03-02', 124);
-- try to insert invalid data
INSERT INTO prices (productID, startDate, endDate, price)
VALUES
(2, '2020-03-02', '2020-04-03', 125);
Entered data overlaps with existing data
-- try to insert invalid data
INSERT INTO prices (productID, startDate, endDate, price)
VALUES
(2, '2020-02-03', '2020-02-04', 126);
Entered data overlaps with existing data
-- valid data
INSERT INTO prices (productID, startDate, endDate, price)
VALUES
(2, '2020-03-03', '2020-04-03', 127);
SELECT * FROM prices;
| id | productID | startDate | endDate | price |
|---|---|---|---|---|
| 1 | 1 | 2020-01-01 | 2020-02-01 | 120.00 |
| 2 | 1 | 2020-02-02 | 2020-03-02 | 121.00 |
| 3 | 1 | 2020-03-03 | 2020-04-03 | 122.00 |
| 4 | 2 | 2020-01-01 | 2020-02-01 | 123.00 |
| 5 | 2 | 2020-02-02 | 2020-03-02 | 124.00 |
| 6 | 2 | 2020-03-03 | 2020-04-03 | 127.00 |