On Sat, 12 Jan 2019, David G. Johnston wrote:
NULL isn't the problem - a check constraint can resolve to unknown in which case it behaves the same as if it resolved as true (i.e., its basically a <check> IS NOT FALSE test in the backend). This is actually a nice feature of check constraints since for nullable columns you don't have to write "col IS NULL OR <the check I really care about>"
David, Thanks for correcting me.
The problem is that check constraints are only applied at time of data change. If you insert a record whose date is 3 days from now the check constraint passes today and (in theory) for the next couple of days. After which the constraint fails - but you are INFORMED ONLY IF THE RECORD IS INSERTED AGAIN. So basically you will not see a problem until you attempt to restore your data on some future date and much of your data fails to restore because those dates are no longer in the future.
I thought that the check constraint applied at data entry, too. If not, then I'll have either wxPython or SQLAlchemy ensure that the next_contact date is later than the contact date.
If you want to check for a future date you should probably also store the date you are comparing against and have the check constraint reference both fields.
The contact date is always entered in a new row, but the next_contact date might not be if there's nothing scheduled. Best regards, Rich
