Example: I have 2 date/time fields in a table, delivery_date and quote date. delivery_date should always have a greater value (later in the calendar) than quote_date.
Is such a requirement enforceable in the schema for the table? That is, if I try to add a new row with a delivery_date before a quote_date, it will reject the change.
>Solution :
Yes, you can use a CHECK constraint for this, for example:
ALTER TABLE mytable ADD CHECK (delivery_date > quote_date);
Assuming delivery_date and quote_date are defined as the MySQL DATE type or another temporal type (i.e. not strings).
CHECK constraints require MySQL 8.0.16 or later. If you must implement this on an older version of MySQL, you would have to write triggers before INSERT and before UPDATE, and use SIGNAL to abort the change if it doesn’t pass your intended constraint.