By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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') <a OR COALESCE(partofb, b || 'a') <b))

B.

Aug 8 '06 #3

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.

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:
>>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') <a OR COALESCE(partofb, b || 'a') <b))

B.
This will work with a minor modification: make "a||'a'" into "a+1" since
"||" is not defined on integers. Thank you.
Aug 8 '06 #5

P: n/a

Bob Stearns wrote:
Brian Tkatch wrote:
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') <a OR COALESCE(partofb, b || 'a') <b))

B.
This will work with a minor modification: make "a||'a'" into "a+1" since
"||" is not defined on integers. Thank you.
Heh, i should have looked at your TABLE definition! Shame on me. :)

Glad i could be of service.

B.

Aug 9 '06 #6

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 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
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 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

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.