Josh Josh - 1 month ago 6
MySQL Question

MySQL - Constraint to Force New Record Field Value to be Greater than Previous Record Field Value

I am trying to create a database schema that forces records in a particular table to confirm to the format represented by the sample data structure below...

+--------------+--------+---------------+-----------------+
| EQUIPMENT_ID | CML_ID | INSPECTION_ID | INSPECTION_DATE |
+--------------+--------+---------------+-----------------+
| B1001-V001 | 1 | 1 | 2010-01-01 |
| B1001-V001 | 1 | 2 | 2011-01-01 |
| B1001-V001 | 1 | 3 | 2012-01-01 |
| B1001-V001 | 1 | 4 | 2013-01-01 |
| B1001-V001 | 1 | 5 | 2014-01-01 |
| B1001-V001 | 1 | 6 | 2015-01-01 |
| B1001-V001 | 1 | 7 | 2016-01-01 |
| B1001-V001 | 1 | 8 | 2017-01-01 |
| B1001-V001 | 1 | 9 | 2018-01-01 |
| B1001-V001 | 1 | 10 | 2019-01-01 |
| B1001-V001 | 2 | 1 | 2010-01-01 |
| B1001-V001 | 2 | 2 | 2011-01-01 |
| B1001-V001 | 2 | 3 | 2012-01-01 |
| B1001-V001 | 2 | 4 | 2013-01-01 |
| B1001-V001 | 2 | 5 | 2014-01-01 |
| B1001-V001 | 2 | 6 | 2015-01-01 |
| B1001-V001 | 2 | 7 | 2016-01-01 |
| B1001-V001 | 2 | 8 | 2017-01-01 |
| B1001-V001 | 2 | 9 | 2018-01-01 |
| B1001-V001 | 2 | 10 | 2019-01-01 |
+--------------+--------+---------------+-----------------+


Basically I want to enforce that the greatest INSPECTION_ID (AUTO INCREMENTED) always has the greatest INSPECTION_DATE (manually entered by the user) for the specific combination of EQUIPMENT_ID and CML_ID.

In layman's terms I want to ensure that the user always enters inspections in a chronological order.

Is there some sort of rule, constraint or schema design I can do to enforce this?

Answer

MySQL does not enforce constraints, so it is irrelevant whether those would work.

You would need to use a trigger to enforce such a condition. If you allow updates, you would need an update and insert trigger.

The insert trigger would look something like this:

CREATE TRIGGER t_trigger_insert ON t BEFORE INSERT
FOR EACH ROW
BEGIN
    IF (EXISTS (SELECT 1
                FROM t t2
                WHERE t2.EQUIPMENT_ID = NEW.EQUIPMENT_ID AND
                      t2.INSPECTION_ID = NEW.INSPECTION_ID AND
                      t2.INSPECTION_DATE > NEW.INSPECTION_DATE
               )
       ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'A later date already exists';
    END IF;
END;
Comments