ALTER TABLE CHECK CONSTRAINT

Question

Error starting at line 35 in command:

ALTER TABLE lee_person
  ADD CONSTRAINT check_person_type 
  CHECK(CASE WHEN UPPER(person_type) = 'EMPLOYEE'
             THEN employment_date IS NOT NULL 
             AND  manager_id IS NOT NULL
             WHEN UPPER(person_type) = 'CLIENT'
             THEN employment_date IS NULL 
             AND  manager_id IS NULL                
        END)

Error report:

SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

Answer 1

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:

1. If the person_type is employee (case-insensitive), employment_date and manager_id must be NOT NULL.

2. If the person_type is client (case-insensitive), employment_date and manager_id must be NULL.

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.