CHECK constraint for restricting NULL values based on another column values

Question

I have this very simple piece that's been bugging my brain for a few hours now:

CREATE TABLE driving_school_questions(

    question VARCHAR2(200),
    picture VARCHAR2(2), 
    CONSTRAINT q_p_chk CHECK ((question LIKE '%see picture%' AND picture IS NOT NULL) 
                                                   AND
                               (question LIKE '% %' OR picture IS NULL))

);

What I'm trying to achieve here is creating a constraint that, if the question field contains 'see picture' then the picture cannot be NULL else, it can be NULL for every question which doesn't contain 'see picture' in it. I had tried other expressions in the CHECK clause but in avail.

These inserts work fine:

INSERT INTO driving_school_questions (question, picture)
VALUES ('blahblah see picture', '23'); --NOT NULL so ok for now

INSERT INTO driving_school_questions (question, picture)
VALUES ('blah blah see picture ', NULL); --It's ok to be NULL(constraint violated)

This is not working:

INSERT INTO driving_school_questions (question, picture)
VALUES ('blah blah', NULL);--it should work but constraint violation

Answer 1

I think you only need a single check, to prevent the single combination of 'see picture' and null:

CREATE TABLE driving_school_questions(
    question VARCHAR2(200),
    picture VARCHAR2(2), 
    CONSTRAINT q_p_chk CHECK (NOT(question LIKE '%see picture%' AND picture IS NULL))
);

This looks for that single combination, and checks that is not what you have.

INSERT INTO driving_school_questions (question, picture)
VALUES ('blahblah see picture', '23');

1 row inserted.

INSERT INTO driving_school_questions (question, picture)
VALUES ('blah blah see picture ', NULL);

Error report -
ORA-02290: check constraint (SCHEMA.Q_P_CHK) violated

INSERT INTO driving_school_questions (question, picture)
VALUES ('blah blah', NULL);

1 row inserted.

As @vkp suggests, you could use a regular expression to check for the 'see picture' part, to prevent false matches, and also case issues:

    CONSTRAINT q_p_chk CHECK (NOT(REGEXP_LIKE(question, '(^|\s)see picture(\s|$)', 'i')
      AND picture IS NULL))

which would mean both of these are OK too:

INSERT INTO driving_school_questions (question, picture)
VALUES ('blahblah isee pictures', null);

INSERT INTO driving_school_questions (question, picture)
VALUES ('See picture', '25');

but this would not be allowed:

INSERT INTO driving_school_questions (question, picture)
VALUES ('See Picture', null);

You may even only want to restrict to the entire string value being just 'see picture', or one of several possible values; which you could also do with slightly modified regex patterns.