Nullable Unique Index

[markmorgan]

CREATE UNIQUE WHERE NOT NULL INDEX ...

The 'WHERE NOT NULL' syntax does not appear to be supported under DB2 UDB v7.2 for Windows, whereas it is under OS/390.

Anyone know why? Anyone know if v8 will support it? Can anyone think of a way around it?

My only option, at the moment, appears to be a normal index, for accessing the data, and programmatically maintaining uniqueness.

Mark.

 

[gregsimpson]

Mark,

not got a DB2 handy at the moment. Is it not that you simply need to say

CREATE UNIQUE INDEX ......

without your NULL part.

Greg

 

[markmorgan]

You can do that, and the index would be created.

Unfortunately, it would treat NULL as any other value and only allow one instance of the NULL column in the whole table.

Mark.