title: Using a case statement in a check constraint
body: <p>i'v been learning SQL for the last week but I am unsure how to correctly add a case statement within a check constraint. Can anybody give me any pointers?</p>
  
  <p>I have the following grade table:</p>
  
  <pre><code>CREATE TABLE Grade
  (
      salary_grade    char(1) NOT NULL CHECK (salary_grade = UPPER(salary_grade)),
          CONSTRAINT ck_grade_scale CHECK(
          CASE 
              WHEN salary_grade = '[A-D]' 
                  THEN salary_scale = 'S1'
              WHEN salary_grade = '[D-G]' 
                  THEN salary_scale = 'S2'
          END)
  
          salary_scale    char(2) DEFAULT 'S1' NOT NULL,
  
          CONSTRAINT pk_grade PRIMARY KEY (salary_grade),
          CONSTRAINT ck_salary_grade CHECK (REGEXP_LIKE(salary_grade, '[A-G]', 'c')),
          --constraint must be either S1 or S2
          CONSTRAINT ck_salary_scale CHECK (salary_scale IN ('S1', 'S2'))
  );
  </code></pre>
  
  <p>I want to check that if the <code>salary_grade</code> is between A-D then the <code>salary_scale</code> must be 'S1' or if the <code>salary_grade</code> is between E-G then it's 'S2'.</p>
  
  <p>I have tried to research this and come up with the latter but however it does not work.. have I structured the code correctly? </p>