| Author |
Message |
Knut Stolze
*nix forums Guru
Joined: 28 Jul 2005
Posts: 755
|
 Posted: Mon Feb 07, 2005 6:45 am Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
ak_tiredofspam@yahoo.com wrote:
| Quote: | Knut,
just imagine:
insert into t(primarykey, notnullunique)values(1,null);
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.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena |
|
| Back to top |
|
 |
ak_tiredofspam@yahoo.com
*nix forums beginner
Joined: 02 May 2005
Posts: 28
|
| Posted: Thu Feb 03, 2005 2:12 pm Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
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 |
|
| Back to top |
|
|
Knut Stolze
*nix forums Guru
Joined: 28 Jul 2005
Posts: 755
|
| Posted: Thu Feb 03, 2005 6:18 am Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
ak_tiredofspam@yahoo.com wrote:
| Quote: | 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.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena |
|
| Back to top |
|
|
Mark Yudkin
*nix forums Guru Wannabe
Joined: 29 May 2005
Posts: 117
|
| Posted: Thu Feb 03, 2005 6:15 am Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
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.
"Mark A" <nobody@nowhere.com> wrote in message
news:XuydnYhxTfYrrpzfRVn-og@comcast.com...
| Quote: | 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?
|
|
|
| Back to top |
|
|
ak_tiredofspam@yahoo.com
*nix forums beginner
Joined: 02 May 2005
Posts: 28
|
| Posted: Wed Feb 02, 2005 10:12 pm Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
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); |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Wed Feb 02, 2005 9:26 pm Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:JlbMd.4715$%u7.1162@fe03.lga...
| Quote: | 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. |
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. |
|
| Back to top |
|
|
Bob Stearns
*nix forums Guru Wannabe
Joined: 01 Jun 2005
Posts: 224
|
| Posted: Wed Feb 02, 2005 8:07 pm Post subject:
Re: UNIQUE WHERE NOT NULL
|
|
|
Mark A wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Wed Feb 02, 2005 7:12 pm Post subject:
UNIQUE WHERE NOT NULL
|
|
|
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? |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
