ALTER TABLE CHECK CONSTRAINT
You can't use a CASE like you're trying to do (CASE WHEN condition-A THEN condition-B AND condition-C).
It looks like you want to enforce the following:
If the
person_typeisemployee(case-insensitive),employment_dateandmanager_idmust beNOT NULL.If the
person_typeisclient(case-insensitive),employment_dateandmanager_idmust beNULL.
If that's the case, try something this instead:
ALTER TABLE lee_person ADD CONSTRAINT check_person_type CHECK ( (UPPER(person_type) = 'EMPLOYEE' AND employment_date IS NOT NULL AND manager_id IS NOT NULL) OR (UPPER(person_type) = 'CLIENT' AND employment_date IS NULL AND manager_id IS NULL) )I've tested this and it works.
Note that the only person_type values it allows are employee and client (case-insensitive). If you want to allow other types (with or without employment_date or manager_id restrictions) you'll need to tack on additional OR conditions.