We help IT Professionals succeed at work.

Check Constraint and NULL in list of values

dlmedici
dlmedici asked
on
Medium Priority
1,761 Views
Last Modified: 2008-02-01
Examine the following check constraint...

ORTHDNTC_TRCTN_DVC_IND INDICATOR with default NULL
      constraint C_ORTHDNTC_TRCTN_D check (ORTHDNTC_TRCTN_DVC_IND is
null or (ORTHDNTC_TRCTN_DVC_IND in ('N','Y',NULL) and
ORTHDNTC_TRCTN_DVC_IND = upper(ORTHDNTC_TRCTN_DVC_IND))),

The column is based on domain INDICATOR, has permissable values of "Y", "N" and null, and defaults to null. My DBA is telling me that DB2 does not like NULL in the list of values. My modeling tool, PowerDesigner 12.0, is telling me this is OK.

So, can the list of values contain NULL? Of not, how do I tell DB2 that the permissable values are "Y", "N" and null?
Comment
Watch Question

hi

you don't have to put the NULL inside the in list since you check if the value is null before you check the in list

momi

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Momi,

Yes, that is what I thought, too. But, PowerDesigner is telling me I have an inconsistency in check parameters, stating, "The values entered in the check parameters tab are inconsistent for numeric and string data types: default does not respect minimum and maximum values, or default does not belong to list of values, or values in list are not included in minimum and maximum values, or minimum is greater than maximum value. Check parameters must be defined consistently." So, PowerDesigner is expecting the NULL in the list of values.

What gives?

Commented:
your first check is to make sure it is null, the second check is to make it is YN or null, it is the OR clause that is causing the error to be raised. Re phrase the check condition to eliminate the OR clause

Author

Commented:
ghp7000,

Can you please elaborate a bit? I cannot see any reason why DB2 should not be able to parse that simple OR. It appears to be quite elemental.
Commented:
simply put if first condition=null             =unknown
second condition=null and third condition=true (unknown and true=unknown)

result=unknown or unknown=unknown
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.