Boolean logic in SQL

Question

I have to do the following by adding a check constraint on the table:

In Tenants, if LeaseExpirationDate isn’t NULL then it must be later than LeaseStartDate.

I am just wondering , can I use the folowing in SQL?

P: LeaseExpirationDate isn't null T: LeaseExpirationDate is later than LeaseStartDate

P->T==-P OR T

Truth table

P     T      P->T=-P OR X
T      F             F
F      U            T
T      T             T

I am not sure if we can do the latter with three valued logic, but it looks like it works given the conditions.

Damien_The_Unbeliever · Accepted Answer · 2017-02-15 08:16:11Z

2

Take advantage of SQL's three-valued logic and the fact that a check constraint needs to not be FALSE, rather than be TRUE, to pass:

CHECK (LeaseExpirationDate > LeastStartDate)

If LeaseExpirationDate is NULL, then the whole condition evaluates as UNKNOWN. Which isn't FALSE and so the constraint isn't violated.

answered Feb 15, 2017 at 8:16

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

For the following one, we do need boolean logic right?:

If LastRentPaidDate in Tenants is the current date, then RentOverdue must be FALSE.