Check Constraint and NULL in list of values

Posted on 2006-11-06
Medium Priority
Last Modified: 2008-02-01
Examine the following check constraint...

      constraint C_ORTHDNTC_TRCTN_D check (ORTHDNTC_TRCTN_DVC_IND is
null or (ORTHDNTC_TRCTN_DVC_IND in ('N','Y',NULL) and

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?
Question by:dlmedici
  • 3
  • 2
LVL 37

Assisted Solution

momi_sabag earned 62 total points
ID: 17881496

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


Author Comment

ID: 17881524

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?
LVL 13

Expert Comment

ID: 17883646
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Author Comment

ID: 17883674

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.
LVL 13

Expert Comment

ID: 17884188
LVL 13

Accepted Solution

ghp7000 earned 188 total points
ID: 17906840
simply put if first condition=null             =unknown
second condition=null and third condition=true (unknown and true=unknown)

result=unknown or unknown=unknown

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question