Enforcing Data Uniqueness On Certain Period on MariaDB

Question

In postgresql, as shown on this question, in PostgreSQL, you can create a rule that allows certain row to be unique during certain time range/period (for example, you can insert different prices on a certain item, provided none of those entries have overlapping period (start/end date)).

However, I am now trying to design an app with possibility of using not just Postgres, but MySQL/MariaDB and MS SQL. On both of those RDBMS (AFAIK) there is no such function to do so. My question is:

1. Should I just enforce this uniqueness on the app instead?
2. If I can, what would be the equivalent method on MySQL/MariaDB and MSSQL?

Thanks, looking forward to your ideas

Answer 1

If you design an app that can adapt different RDBMSes, you'd better not to use a feature that relies on a certain RDBMS, unless it can reduces the overall code complexity. Otherwise, this will complicate the version control and distribution process.

Answer 2

MySQL sample. Trigger logic is used.

-- test table
CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value INT NOT NULL,
  start_time INT NOT NULL,
  end_time INT NOT NULL,
  CHECK (start_time < end_time));

-- trigger which forbids overlapping durin insertion
CREATE TRIGGER check_on_insert
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  IF EXISTS ( SELECT NULL
              FROM test
              WHERE NEW.value = test.value
                AND NEW.start_time <= test.end_time
                AND test.start_time <= NEW.end_time
            ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Overlapping detected, aborting';
  END IF;
END

-- trigger which forbids overlapping durin updation
CREATE TRIGGER check_on_update
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
  IF EXISTS ( SELECT NULL
              FROM test
              WHERE NEW.value = test.value
                AND NEW.start_time <= test.end_time
                AND test.start_time <= NEW.end_time
                AND NEW.id <> test.id
            ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Overlapping detected, aborting';
  END IF;
END

-- insert non-overlapped rows, update one row without overlapping
INSERT INTO test VALUES 
  (DEFAULT, 1, 1,5),
  (DEFAULT, 1, 11,15),
  (DEFAULT, 2, 1,5),
  (DEFAULT, 3, 1,5);
UPDATE test SET start_time = 8 WHERE start_time = 11;
SELECT * FROM test;

id	value	start_time	end_time
1	1	1	5
2	1	8	15
3	2	1	5
4	3	1	5

-- insert overlapped row
INSERT INTO test VALUES (DEFAULT, 1, 3,8);

Overlapping detected, aborting

-- update existing row which produces overlapping
UPDATE test SET start_time = 3 WHERE start_time = 8;

Overlapping detected, aborting

-- update which produces non-legal range value
UPDATE test SET start_time = 23 WHERE start_time = 8;

Check constraint 'test_chk_1' is violated.

fiddle