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.
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.
This is kind of work-aroundy answer, but a possible way to go nonetheless
In my projects I store dates as int unsigned timestamps, so this questions immediately makes me think of taking an advantage of having an unsigned field.
If it's possible to modify the table, how about this schema:
| Quote date (timestamp) | Wait time (in seconds/10) |
|---|---|
| 1648771000 | 1080 |
In this example, quote date has a timestamp of 1648771000 (March 31, '22, 23:56:40), and the parcel is delivered in 1080 * 10 = 10800 seconds (that's 3 hours).
This way of storing data has a couple of benefits
date occupies 5 bytes, that 10 for two of them. int unsigned occupies 4 bytes, that's 8 for two of them.mediumint unsigned for wait time and store data with precision to seconds, although won't allow wait time greater than 194 days (16777213 max value). BUT if we store wait time as seconds / 10 (as per my table above), that's already 1940 days max. mediumint unsigned occupies 3 bytes, that would be 7 bytes in total (-30% compared to using date)It's still easy to manage data in this schema - delivery date would be quote date + wait time