If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > UNIQUE constrains on the nullable column

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 21
UNIQUE constrains on the nullable column

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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 267
Quote:
Originally Posted by nassarane View Post
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
__________________
--
Lennart
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 21
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2006
Location: Belgium
Posts: 2,084
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
__________________
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified
http://www.infocura.be
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,450
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 21
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.
Reply With Quote
  #7 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,450
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 21
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.
Reply With Quote
  #9 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,450
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 267
Quote:
Originally Posted by nassarane View Post
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
__________________
--
Lennart
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 21
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.
Reply With Quote
  #12 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,450
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 21
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On