Oracle/DB2 null key index

This issue is coming from when migrate DB2 unique index to Oracle.

Concept Definition

- KEY of an index:    A "KEY" is the set of columns making up an index. That is it may be one column or multiple columns.

- NULL KEY of an index: a "NULL KEY" that is a key where all columns are NULL.

DB2 only one "NULL KEY" may exist in a unique index.

When table and index are created as

CREATE TABLE TAB (A DECIMAL(6) not null,
                  B CHAR(10),
                  C DECIMAL(6),
                  PRIMARY KEY(A));

CREATE UNIQUE INDEX IDX ON TAB(B,C);

Following SQL will fail on 2nd statement:

INSERT INTO TAB VALUES(1, NULL, NULL);
INSERT INTO TAB VALUES(2, NULL, NULL);

This is the error message:

DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.
During SQL processing it returned:SQL0803N
One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement
are not valid because the primary key, unique constraint or unique index identified by "IDX" 
constrains table "0000000201" from having duplicate values for the index key.  SQLSTATE=23505

Oracle multiple NULL keys may exist in a unique index.

The same operation above can be executed on Oracle without any failure.

This is because Oracle does not index NULL KEYS; whatever unique or not, an Oracle index does not include rows if all indexed columns are null.
This cause a result is that UNIQUE index in Oracle simply does not know about NULL keys. It's literallxy blind to the multiple NULL KEYS issue.

DB2 "unique where not null" index

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

So if we create index using:

CREATE UNIQUE WHERE NOT NULL INDEX IDX ON TAB(B,C)

Then we can get same result as Oracle, i.e,; both insert statements can be executed successfully.

Oracle support non-duplicated null index

Solution: to add a constant value field into index, so that the index key could not be null.

For example we create above index using:

CREATE UNIQUE INDEX IDX ON TAB(B,C,1)

The constant '1' does not means the 1st column, it is a constant; so the index key must not be null because the 3rd column is a constant 1.

The solution for migrating UNIQUE index

If all indexed columns are null-able, a constant 1 should be added into Oracle index definition.

For example DB2 index definition

CREATE UNIQUE INDEX IDX ON TAB(B,C)

if both column B and C are null-able, this index should be redefined in Oracle as:

CREATE UNIQUE INDEX IDX ON TAB(B,C,1)

And if there is either column is "not null", it's not necessary to add the constant 1.

In Summary

CREATE UNIQUE WHERE NOT NULL INDEX IDX ON TAB(B,C)   # on DB2

=equal to=

CREATE UNIQUE INDEX IDX ON TAB(B,C)                  # on Oracle

CREATE UNIQUE INDEX IDX ON TAB(B,C)                  # on DB2

=equal to=

CREATE UNIQUE INDEX IDX ON TAB(B,C,1)                # on Oracle