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

Can `question` be `null`? Presumably not, in which case you should have *that* as a constraint (write the words `NOT NULL` right after the column definition). If it *can* be `null` then the check constraint will have to deal with that possibility - and you will need to share what the business requirement is in that case. — , Dec 29 '16 at 23:03

Alex Poole · Accepted Answer · 2016-12-29T18:02:30.920

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.

may be using `regexp_like` to include word boundaries is a better option so something like `isee pictures` won't be matched. — Vamsi Prabhala, Dec 29 '16 at 17:56
Thanks Poole for everything. I'll take the first variant which is fine enough, the second one is too advanced for me and I don't need it anyway for my purpose. — udarH3, Dec 29 '16 at 18:27

CHECK constraint for restricting NULL values based on another column values

1 Answers1