7

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?

David Faber
  • 12,277
  • 2
  • 29
  • 40
user3414871
  • 99
  • 1
  • 1
  • 8
  • Do you get some error? If so, update your question, it may help finding the problem. – Hugo Sousa Jan 26 '15 at 20:38
  • @Hugo Sousa I'm getting a right parenthesis missing when trying to drop. Also says the table does not exist – user3414871 Jan 26 '15 at 20:52
  • I'm really new to this only started learning this a couple of weeks ago so please bear with me – user3414871 Jan 26 '15 at 20:52
  • Generally, you shouldn't use `CASE` where Boolean logic is allowed (e.g. `WHERE` clauses and check constraints). It's intended use is to allow you to use Boolean logic where it is not typically allowed (e.g. the column list in `SELECT` statements). – Allan Jan 26 '15 at 21:24
  • @user3414871 You already have 3 good answers. You should give them credit by accepting the one that fits your needs best. – Hugo Sousa Jan 27 '15 at 13:28
  • @HugoSousa I'm sorry I totally forgot to do this and I do appreciate all the help. – user3414871 Jan 28 '15 at 15:23
  • Thank you guys - this is a big learning curve – user3414871 Jan 28 '15 at 15:24

3 Answers3

8

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.

David Faber
  • 12,277
  • 2
  • 29
  • 40
  • 1
    The first check on `salary_grade` alone isn't really needed either, since `ck_grade_scale` covers that too. – Alex Poole Jan 26 '15 at 21:47
  • Yes, you're probably right, but then I would have to change my `ELSE` in the `CASE`. Strictly speaking too a regex is probably more expensive than one would like; `BETWEEN` is better. – David Faber Jan 26 '15 at 21:51
  • @DavidFaber Thanks dude.. You been very helpful. It's a big learning curve when you just getting into SQL. I thought that with only a few keywords it would be easy to learn but things are never as easy as they look – user3414871 Jan 27 '15 at 10:56
5

A case has to be compared to something, which is why you are getting the missing right parenthesis error. Unless you particularly want a case, you can just check the combination with and/or:

CONSTRAINT ck_grade_scale CHECK(
    (salary_grade BETWEEN 'A' AND 'D' AND salary_scale = 'S1')
    OR (salary_grade BETWEEN 'D' AND 'G' AND salary_scale = 'S2')),

SQL Fiddle demo.

As Parado has said, you can't use constraints to set column values conditionally, only to restrict them. You could potentially use a virtual column for the scale, but it would mean putting part of a look-up table into the DDL rather than the data, which seems a bit strange.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I kinda already know how to do this, as explained in 'Oracle SQL by Example' but the book does not tell me how to use case statements in Check constraints. This is what I want to learn – user3414871 Jan 26 '15 at 20:56
  • @user3414871: Perhaps that book doesn't give you an example of this use because it's not a good use for `CASE`. – Allan Jan 26 '15 at 21:48
2

Check Constraints is used to test data before insert to protect data structure from fake data. Actually we use case in select statement. You can't use it for conditional insert. If you want to change data for particular column before insert you need to use trigger or you can also use virtual column but which has some restrictions.

More information you can find here

Robert
  • 25,425
  • 8
  • 67
  • 81
  • Okay. This helps.. Thanks. What I was thinking was if grade is entered then the salary_scale would be automatically put in. Is that possible? – user3414871 Jan 26 '15 at 21:03
  • 1
    @user3414871 Yes it's possible by using triggers or virtual columns as I mentioned above. – Robert Jan 26 '15 at 21:06
  • Okay.. i'm not trying to assign values to the columns, just trying to ensure the values of the columns are either S1 or S2. i.e S1 if grade A – user3414871 Jan 26 '15 at 21:23
  • 2
    @user3414871 Yes it's Alex Poole``s solution `CONSTRAINT ck_grade_scale CHECK((salary_grade BETWEEN 'A' AND 'D' AND salary_scale = 'S1') OR (salary_grade BETWEEN 'D' AND 'G' AND salary_scale = 'S2'))` – Robert Jan 26 '15 at 21:30