454,237 Members | 1,242 Online
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

# Best way to insure four columns are notthe same pair wise in thepresence of NULLS

 P: n/a Consider create table t( .. .. .. a integer, b varchar(25), partofa integer, partofb varchar(25). .. .. .. I want to write a check constraint which enforces not(a=partofa and b=partofb) whenever partofa and partofb are not both null. What I came up with was an ugly clause with lots of 'is null's and 'is not null's. Surely there is an elegant solution for this. Aug 8 '06 #1
12 Replies

 P: n/a Hello. If you are at DB2 for LUW: --- not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) --- If you are at iSeries: --- not (a is not distinct from partofa and b is not distinct from partofb) --- Sincerely, Mark B. Consider create table t( . . . a integer, b varchar(25), partofa integer, partofb varchar(25). . . . I want to write a check constraint which enforces not(a=partofa and b=partofb) whenever partofa and partofb are not both null. What I came up with was an ugly clause with lots of 'is null's and 'is not null's. Surely there is an elegant solution for this. Aug 8 '06 #2

 P: n/a Bob Stearns wrote: Consider create table t( . . . a integer, b varchar(25), partofa integer, partofb varchar(25). . . . I want to write a check constraint which enforces not(a=partofa and b=partofb) whenever partofa and partofb are not both null. What I came up with was an ugly clause with lots of 'is null's and 'is not null's. Surely there is an elegant solution for this. CHECK((partofa IS NULL AND partofb IS NULL) OR (COALESCE(partofa, a || 'a')

 P: n/a 4.****@mail.ru wrote: Hello. If you are at DB2 for LUW: --- not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) --- If you are at iSeries: --- not (a is not distinct from partofa and b is not distinct from partofb) --- Sincerely, Mark B. >>Considercreate table t(...a integer,b varchar(25),partofa integer,partofb varchar(25)....I want to write a check constraint which enforces not(a=partofa andb=partofb) whenever partofa and partofb are not both null. What I cameup with was an ugly clause with lots of 'is null's and 'is not null's.Surely there is an elegant solution for this. Thank you. That is very clever. I should have stated I was using DB2 for LUW v8.1.9. Having the DISTINCT predicate would be very nice. Do you know if it is in DB2 v9? Aug 8 '06 #4

 P: n/a Brian Tkatch wrote: Bob Stearns wrote: >>Considercreate table t(...a integer,b varchar(25),partofa integer,partofb varchar(25)....I want to write a check constraint which enforces not(a=partofa andb=partofb) whenever partofa and partofb are not both null. What I cameup with was an ugly clause with lots of 'is null's and 'is not null's.Surely there is an elegant solution for this. CHECK((partofa IS NULL AND partofb IS NULL) OR (COALESCE(partofa, a || 'a')

 P: n/a Bob Stearns wrote: Brian Tkatch wrote: Bob Stearns wrote: >Considercreate table t(...a integer,b varchar(25),partofa integer,partofb varchar(25)....I want to write a check constraint which enforces not(a=partofa andb=partofb) whenever partofa and partofb are not both null. What I cameup with was an ugly clause with lots of 'is null's and 'is not null's.Surely there is an elegant solution for this. CHECK((partofa IS NULL AND partofb IS NULL) OR (COALESCE(partofa, a || 'a')

 P: n/a 4.****@mail.ru wrote: Hello. If you are at DB2 for LUW: --- not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) --- If you are at iSeries: --- not (a is not distinct from partofa and b is not distinct from partofb) --- Sincerely, Mark B. Consider create table t( . . . a integer, b varchar(25), partofa integer, partofb varchar(25). . . . I want to write a check constraint which enforces not(a=partofa and b=partofb) whenever partofa and partofb are not both null. What I came up with was an ugly clause with lots of 'is null's and 'is not null's. Surely there is an elegant solution for this. Is following thinking right? If (a is null and partofa is null and b = '1' and partofb = '1'), Condition "partofa and partofb are not both null" is satisfied. 1) not(a=partofa and b=partofb) =: not(Unknown and True) =: not(Unknown) =: Unknown 2) not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) =: not( (Unknown or True) and (True or False) ) =: not( True and True) =: False Condition 1) satisfy CHECK condition. Condition 2) don't satisfy CHECK condition. Aug 11 '06 #7

 P: n/a 4.****@mail.ru wrote: Hello. If you are at DB2 for LUW: --- not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) --- If you are at iSeries: --- not (a is not distinct from partofa and b is not distinct from partofb) --- Sincerely, Mark B. Consider create table t( . . . a integer, b varchar(25), partofa integer, partofb varchar(25). . . . I want to write a check constraint which enforces not(a=partofa and b=partofb) whenever partofa and partofb are not both null. What I came up with was an ugly clause with lots of 'is null's and 'is not null's. Surely there is an elegant solution for this. Is following thinking right? If (a is null and partofa is null and b = '1' and partofb = '1'), Condition "partofa and partofb are not both null" is satisfied. 1) not(a=partofa and b=partofb) =: not(Unknown and True) =: not(Unknown) =: Unknown 2) not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) =: not( (Unknown or True) and (True or False) ) =: not( True and True) =: False Condition 1) satisfy CHECK constraint. Condition 2) don't satisfy CHECK constraint. Aug 11 '06 #8

 P: n/a Bob, did I misunderstand? whenever partofa and partofb are not both null Does it mean "partofa IS NOT NULL AND partofb IS NOT NULL" Aug 12 '06 #9

 P: n/a Tonkuma wrote: Bob, did I misunderstand? whenever partofa and partofb are not both null Does it mean "partofa IS NOT NULL AND partofb IS NOT NULL" And do you want if both of a and partofa are null, assume they are equal? Aug 12 '06 #10

 P: n/a Tonkuma wrote: Tonkuma wrote: >>Bob, did I misunderstand? >>>whenever partofa and partofb are not both null Does it mean "partofa IS NOT NULL AND partofb IS NOT NULL" And do you want if both of a and partofa are null, assume they are equal? Yes and Yes Aug 12 '06 #11

 P: n/a Bob Stearns wrote: Tonkuma wrote: Tonkuma wrote: >Bob, did I misunderstand?whenever partofa and partofb are not both nullDoes it mean "partofa IS NOT NULL AND partofb IS NOT NULL" And do you want if both of a and partofa are null, assume they are equal? Yes and Yes So, if you would use following CHECK constraint, you can't insert data like (a is null and partofa is null and b = '1' and partofb = '1'). Because CHECK constraint would return False. CHECK( not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) ) Is it your requirement? (This data doesn't satisfy condition "partofa IS NOT NULL AND partofb IS NOT NULL") Aug 12 '06 #12

 P: n/a Tonkuma wrote: Bob Stearns wrote: >>Tonkuma wrote: >>>Tonkuma wrote: Bob, did I misunderstand? >whenever partofa and partofb are not both nullDoes it mean "partofa IS NOT NULL AND partofb IS NOT NULL" And do you want if both of a and partofa are null, assume they areequal? Yes and Yes So, if you would use following CHECK constraint, you can't insert data like (a is null and partofa is null and b = '1' and partofb = '1'). Because CHECK constraint would return False. CHECK( not ( (a=partofa or coalesce(a, partofa) is null) and (b=partofb or coalesce(b, partofb) is null) ) ) Is it your requirement? (This data doesn't satisfy condition "partofa IS NOT NULL AND partofb IS NOT NULL") Exactly. Consider (a, b) as primary key (it isn't, but that discussion is for another day) and (partofa, partofb) as a recursive foreign key. The requirement is that if (partofa, partofb) is not (null, null) then it must be different from (a, b); i. e. not a reference to itself. Aug 12 '06 #13

### This discussion thread is closed

Replies have been disabled for this discussion.