UNIQUE constrains on the nullable column

nassarane · #1 (**permalink**) 02-26-13, 06:41

Hi all,

I tried to create a unique index with null values supporting the following command:

Code:

CREATE UNIQUE WHERE NOT NULL INDEX ON UNQ_IDX MYTABLE (MYFOREIGNKEY_ID)

DB2 and answers:

Code:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "WHERE NOT NULL" was found following "CREATE
UNIQUE ".  Expected tokens may include:  "<space>".  SQLSTATE=42601

I work on DB2 V9.7 on AIX.

In the DB2 for z/OS documentation :

Code:

>>-CREATE--+----------------------------+--INDEX--index-name---->
           '-UNIQUE--+----------------+-'
                     '-WHERE NOT NULL-'

but in the DB2 for Linux, UNIX, and Windows documentation :

Code:

>>-CREATE--+--------+--INDEX--index-name------------------------>
           '-UNIQUE-'

the WHERE NOT NULL is not available !

I am looking for a way to create a UNIQUE constrains on the nullable column.

this command line does not work (a error is generated)

Code:

ALTER TABLE MYTABLE ADD CONSTRAINT UNQ UNIQUE (MYFOREIGNKEY_ID)

Thanks.

Nassa.

lelle12 · #2 (**permalink**) 02-26-13, 07:32

Quote:

Originally Posted by nassarane

Hi all,

I tried to create a unique index with null values supporting the following command:

Code:

CREATE UNIQUE WHERE NOT NULL INDEX ON UNQ_IDX MYTABLE (MYFOREIGNKEY_ID)

DB2 and answers:

Code:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "WHERE NOT NULL" was found following "CREATE
UNIQUE ".  Expected tokens may include:  "<space>".  SQLSTATE=42601

I work on DB2 V9.7 on AIX.

In the DB2 for z/OS documentation :

Code:

>>-CREATE--+----------------------------+--INDEX--index-name---->
           '-UNIQUE--+----------------+-'
                     '-WHERE NOT NULL-'

but in the DB2 for Linux, UNIX, and Windows documentation :

Code:

>>-CREATE--+--------+--INDEX--index-name------------------------>
           '-UNIQUE-'

the WHERE NOT NULL is not available !

I am looking for a way to create a UNIQUE constrains on the nullable column.

this command line does not work (a error is generated)

Code:

ALTER TABLE MYTABLE ADD CONSTRAINT UNQ UNIQUE (MYFOREIGNKEY_ID)

Thanks.

Nassa.

Not sure I understand the problem, does this article help:

https://www.ibm.com/developerworks/m...exes26?lang=en

nassarane · #3 (**permalink**) 02-26-13, 07:38

lelle12

Your link is the z/OS documentation.
I work on AIX system not z/OS.

This option WHERE NOT NULL does not existe on Linux, UNIX and Windows system.

The Linux, UNIX and Windows documentation is : http://pic.dhe.ibm.com/infocenter/db...Fr0000919.html

Nassa.

przytula_guy · #4 (**permalink**) 02-26-13, 08:02

not all that is available on one platform is also available on all platforms
use the syntax from the platform you are working on
unique constraint : col must be not null otherwise use unique index instead

Pat Phelan · #5 (**permalink**) 02-26-13, 08:30

To put the post from przytula_guy in another context, the zOS version of DB2 comes from a different code base with different options/syntax than UDB/LUW versions of DB2. The feature that you want (allowing NULL values in a UNIQUE constraint/index) is not available in the DB2 that you are running.

The only answers that I can see is to either deal without the NULL values or upgrade to zOS DB2 which in turn implies that you need to either have or buy a zOS machine to run it.

-PatP

nassarane · #6 (**permalink**) 02-26-13, 09:36

Ok,

I do not need to INDEX, but only UNQIUE CONSTRAINT.
So giving my case there is no solution.
I have to manage the software UNIQUE CONSTRAINT ?

Thanks.

Nassa.

Pat Phelan · #7 (**permalink**) 02-26-13, 09:55

You can create a pseudo unique constraint where the NULL meta-value is allowed duplicates but all other values are unique using a trigger. You could also apply the same kind of exception processing for a value like 0 or 1 if that suited your needs too.

-PatP

nassarane · #8 (**permalink**) 02-26-13, 10:03

Ok,

But I am not DBA, I don't know DB2.
I just adapted a MySQL DDL to DB2.
I did not know before DB2.

Thanks.

Nassa.

Pat Phelan · #9 (**permalink**) 02-26-13, 10:24

One of the people that does more MySQL to DB2 migrations might know of a tool that does this automagically. It seems to me that there is a migration tool offered by IBM that will do this for you, but I don't know what it is called.

-PatP

lelle12 · #10 (**permalink**) 02-26-13, 11:53

Quote:

Originally Posted by nassarane

lelle12

Your link is the z/OS documentation.
[....]
Nassa.

Huh, the link I provided is most certainly for LUW, what gives you the impression that it concerns z/OS? I'll repeat the link:

https://www.ibm.com/developerworks/m...exes26?lang=en

nassarane · #11 (**permalink**) 02-26-13, 11:54

Ok,

The IBM convertion tools is IBM Data Movement Tool : https://www14.software.ibm.com/webap...US&source=idmt
I tried to use it.
It generated this :

Code:

CREATE UNIQUE INDEX "SCH"."UQ692_MYTABLE" ON "SCH"."MYTABLE"
(
"MYFOREIGNKEY_ID"  
)
ALLOW REVERSE SCANS
;

It's not good, this solution supports only one key value to NULL.

Thanks.

Nassa.

Pat Phelan · #12 (**permalink**) 02-26-13, 12:02

While I haven't tested it, the URL that Lelle12 posted ought to work in any current version of DB2 as long as there is a suitable PK or unique column to use.

-PatP

nassarane · #13 (**permalink**) 03-06-13, 12:08

Hi all,

I tried the Serge RIELAU method suggested by Lelle12. It works!
Thank you very much for your help, and Lelle12 Pat Phelan.

Nassa.

Internet Services

Web Development

Digital Marketing

Consumer Tech