UNIQUE WHERE NOT NULL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark A - 02 Feb 2005 20:12 GMT

UNIQUE WHERE NOT NULL is supported in DB2 for z/OS, which allows for
duplicate null values to exist on a unique constraint.

Anyone know if or when this is coming to DB2 for LUW?

Reply to this Message

Bob Stearns - 02 Feb 2005 21:07 GMT

> UNIQUE WHERE NOT NULL is supported in DB2 for z/OS, which allows for
> duplicate null values to exist on a unique constraint.
>
> Anyone know if or when this is coming to DB2 for LUW?

Not being IBM I can't answer the original question, but I can suggest a
reasonably easy to do the same thing. Suppose a table t, with at least
primarykey, and notnullunique columns. The following (barring any typing
or memory lapses on my part) should do what what you want.

alter table t
add column pseudokey generated always as
colaesce(notnullunique, primarykey);

alter table t
add constraint uniqpseudo unique (pseudokey);

These can be combined into one statement, but the intent is clearer as two.

Reply to this Message

Mark A - 02 Feb 2005 22:26 GMT

> Not being IBM I can't answer the original question, but I can suggest a
> reasonably easy to do the same thing. Suppose a table t, with at least
[quoted text clipped - 9 lines]
>
> These can be combined into one statement, but the intent is clearer as two.

2 Big assumptions need to be made for this to work:

1) There are no data type or length issues between the two columns
2) There is no way that the primary key could be a valid value on the other
column that you are testing for uniqueness.

Reply to this Message

ak_tiredofspam@yahoo.com - 02 Feb 2005 23:12 GMT

it's a brilliant idea, it only needs a little modification:

alter table t
add column pseudokey generated always as
(case when notnullunique is null then primarykey end);
create unique index u_ind on the_table(pseudokey, notnullunique);

Reply to this Message

Knut Stolze - 03 Feb 2005 07:18 GMT

> it's a brilliant idea, it only needs a little modification:
>
> alter table t
> add column pseudokey generated always as
> (case when notnullunique is null then primarykey end);
> create unique index u_ind on the_table(pseudokey, notnullunique);

Why do you need the modifications? The COALESCE is exactly the same
(semantically) as the CASE expression you used. And the UNIQUE constraint
creates a unique index under the covers too.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

ak_tiredofspam@yahoo.com - 03 Feb 2005 15:12 GMT

Knut,

just imagine:

insert into t(primarykey, notnullunique)values(1,null);

in Bob's index there is (1);
in mine there is (1,null);

insert into t(primarykey, notnullunique)values(2,1);
---- should be OK, correct?

that would add another (1) to Bob's index - FAIL, IT'S A UNIQUE INDEX
in mine there are (1,null) and (null,1) - still OK

When I was typing
case when notnullunique is null then primarykey end
I meant

case when notnullunique is null then primarykey ELSE NULL end

which is not equivalent to COALESCE, which is

case when notnullunique is null then primarykey ELSE NOTNULLUNIQUE end

What do you think?
Alexander

Reply to this Message

Knut Stolze - 07 Feb 2005 07:45 GMT

> Knut,
>
[quoted text clipped - 4 lines]
> in Bob's index there is (1);
> in mine there is (1,null);

You are correct. I missed the part where the index is created on two
columns. Now it makes sense to me.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

Mark Yudkin - 03 Feb 2005 07:15 GMT

I suggest you open a "requirement" through your local IBM support. That way,
you at least get noticed. And the more they receive such requirements, the
better the chances that IBM will start to address these sorts of unnecessary
and frustrating incompatibilities.

And yes, it's a very big nuisance. Some things take years to move from
mainframe DB2 to UDB (e.g. versioned packages), others, like results sets in
embedded CALLs, UNIQUE WHERE NOT NULL, or an intelligent REBIND command,
still haven't been done.

> UNIQUE WHERE NOT NULL is supported in DB2 for z/OS, which allows for
> duplicate null values to exist on a unique constraint.
>
> Anyone know if or when this is coming to DB2 for LUW?

Reply to this Message