Solved

Check Constraint and NULL in list of values

Posted on 2006-11-06
6
1,552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 

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

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

707 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