Solved

Check Constraint and NULL in list of values

Posted on 2006-11-06
6
1,544 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?
0
Comment
Question by:dlmedici
  • 3
  • 2
6 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 31 total points
ID: 17881496
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
0
 

Author Comment

by:dlmedici
ID: 17881524
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?
0
 
LVL 13

Expert Comment

by:ghp7000
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
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:dlmedici
ID: 17883674
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.
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17884188
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 94 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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

808 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