Using a case statement in a check constraint

Question

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?

I have the following grade table:

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'))
);

I want to check that if the salary_grade is between A-D then the salary_scale must be 'S1' or if the salary_grade is between E-G then it's 'S2'.

I have tried to research this and come up with the latter but however it does not work.. have I structured the code correctly?

Answer 1

I think you can do the following:

CREATE TABLE Grade
(
  salary_grade    char(1) NOT NULL CHECK (REGEXP_LIKE(salary_grade, '[A-G]', 'c')),
  salary_scale    char(2) DEFAULT 'S1' NOT NULL,
  CONSTRAINT pk_grade PRIMARY KEY (salary_grade),
  CONSTRAINT ck_grade_scale CHECK ( REGEXP_LIKE(salary_grade, '[A-D]', 'c') AND salary_scale = 'S1'
                                 OR REGEXP_LIKE(salary_grade, '[E-G]', 'c') AND salary_scale = 'S2' )
);

Please see SQL Fiddle schema here.

You don't need the UPPER() constraint on salary_grade since the regex check will suffice (you're already checking to make sure it's an uppercase letter between A and G). I don't think the constraint on salary_scale alone is necessary either since it would be contained, logically, in the last constraint.

UPDATE

Here is how you might do it with a CASE statement:

CREATE TABLE Grade
(
  salary_grade    char(1) NOT NULL CHECK (REGEXP_LIKE(salary_grade, '[A-G]', 'c')),
  salary_scale    char(2) DEFAULT 'S1' NOT NULL,  
  CONSTRAINT pk_grade PRIMARY KEY (salary_grade),
  CONSTRAINT ck_grade_scale CHECK ( salary_scale = CASE WHEN REGEXP_LIKE(salary_grade, '[A-D]', 'c') THEN 'S1' ELSE 'S2' END )
);

Please see SQL Fiddle schema here.