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.

CodePudding user response：

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.

CodePudding user response：

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:

_{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).}

What for?

This way of storing data has a couple of benefits

1. No need for a check: since wait time is unsigned, neither insert nor update would allow for a negative value
2. 20% space saved: a date occupies 5 bytes, that 10 for two of them. int unsigned occupies 4 bytes, that's 8 for two of them.
3. Even more space saved if we use 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