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

[Bob Stearns]

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.

[4.spam]

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.

[Brian Tkatch]

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.

[Bob Stearns]

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?

[Bob Stearns]

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.

[Brian Tkatch]

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.

[Tonkuma]

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.

[Tonkuma]

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.

[Tonkuma]

Bob, did I misunderstand?

whenever partofa and partofb are not both null

Does it mean "partofa IS NOT NULL AND partofb IS NOT NULL"

[Tonkuma]

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?

[Bob Stearns]

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

[Tonkuma]

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

[Bob Stearns]

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.