CODEWITHSUNDEEP
sqloracle-database
olmnt
olmnt

Reputation: 157

Why doesn't this check constraint work?3

I have this table question(id_q, question,picture, answer) and I need to make a constraint that doesn't allow me to insert a new question in the table if the question contains in it's text 'indicator semnification' and the picture is null.

I tried this way:

ALTER TABLE question ADD
CONSTRAINT CK_question check (((question like '%indicator semnification%') and 
                               (picture in(null))) or 
                               (question not like '%indicator semnification%'));

but it doesn't works.

Upvotes: 0

Views: 148

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is your constraint:

ALTER TABLE question
    ADD CONSTRAINT CK_question check (((question like '%indicator semnification%') and
                                      (picture in(null))) or
                                      (question not like '%indicator semnification%')
                                     )

The problem is probably the picture in (null). This will never return true. Try this:

ALTER TABLE question
    ADD CONSTRAINT CK_question check (((question like '%indicator semnification%') and
                                      (picture is null)) or
                                      (question not like '%indicator semnification%')
                                     )

Also, if you are using MySQL, you can express constraints, but they are not implemented.

EDIT:

The above fixes the logic in your version. I think the logic you really want is:

ALTER TABLE question
    ADD CONSTRAINT CK_question check ((question not like '%indicator semnification%') or 
                                      (picture is not null)
                                     ) ;

Or, equivalently (when question is not null):

ALTER TABLE question
    ADD CONSTRAINT CK_question check (not ((question like '%indicator semnification%') and 
                                           (picture is null))
                                     ) ;

Upvotes: 2

Rahul
Rahul

Reputation: 77846

Try modifying your constraint definition like below, using () your condition set(s)

ALTER TABLE question ADD
CONSTRAINT CK_question check (question LIKE '%indicator semnification%'  
                               OR
                               (picture IS NULL  
                                OR  
                               question NOT LIKE '%indicator semnification%'));

Upvotes: 0

Related Questions