Posted by: Serge Rielau

Some of you may know that I am engaged in enabling Oracle applications to DB2.
One of the frequent "issues" we see are differences between the way how indexing works in Oracle and DB2 and, more specifically about different semantics for unique indexes.
 
In DB2 a unique index requires that no two keys are the same.
Sounds simple and clear on the surface, but not so clear when you consider NULLs.
Generally speaking a NULL cannot be compared to a NULL and thus it is debatable whether one should allow more than one NULL key in a unique index. In the DB2 world only one NULL key may exist in a unique index.
In Oracle however you can have multiple NULL keys.

So does that mean in Oracle a NULL is not equal to a NULL? Not really.
Let's look closer and define what a "key" is.
A  "key" is the set of columns making up an index. That is it may be one column or multiple columns.
So when we talk about a NULL key that is a key where all columns are NULL.
So when I say you can have multiple NULL keys in Oracle that means that a key will all NULLs can appear multiple times.
But a key where at least one column is NOT NULL cannot appear multiple times.

So this key is legal for a unique index in Oracle:

(NULL, NULL)
(NULL, NULL)

But this key is not:

(NULL, 1)
(NULL, 1)

So even in Oracle NULL's are equal to NULLs most of the times as far as unique keys are concerned.
So, if DB2 and Oracle are so close why are they not the same?

Simple: Oracle does not index NULL keys ever. Unique or not there is no such thing an an index with an all NULL key.
As a result a UNIQUE index in Oracle simply does not know about NULL keys. It's literally blind to the issue.

Well, that's all nice as an explanation, but if you have a need for a unique index with this Oracle behavior, what do you do?
What is needed is an extra column that makes all NULL keys unique but does not interfere with uniqueness enforcement if there is any NULL column in the key.

• Let's assume the following example (in Oracle):

  CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c1 VARCHAR(10));
  
  CREATE UNIQUE INDEX idx ON T(c1);

  In DB2 you would translate that as such:

  CREATE TABLE T(pk  INT, c1 VARCHAR(10),
                 idx INT GENERATED ALWAYS AS (CASE WHEN c1 IS NULL THEN pk ELSE NULL END));
  CREATE UNIQUE INDEX idx ON T(c1, idx);

• How if there is more than one column in the index?

  CREATE TABLE T(pk INT NOT NULL PRIMARY KEY,
                 c1 VARCHAR2(10),
                 c2 DATE);
  CREATE UNIQUE INDEX idx ON T(c1, c2);

  In DB2 you would translate that as such (note the AND condition!):

  CREATE TABLE T(pk  INT,
                 c1  VARCHAR2(10),
                 c2  DATE,
                 idx INT GENERATED ALWAYS AS (CASE WHEN c1 IS NULL AND c2 IS NULL 
                                                   THEN pk ELSE NULL END));
  CREATE UNIQUE INDEX idx ON T(c1, c2, idx);

• But what if the primary key is long, has multiple columns or just doesn't exist?
        If that is the case you can introduce an identity column to get an artificial small key, a so called abstract primary key.
        Let's assume we have no primary key in this example:

  CREATE TABLE T(c1 VARCHAR2(10));
  CREATE UNIQUE INDEX idx ON T(c1);

  In DB2 you would translate that as such:

  CREATE TABLE T(c1 VARCHAR(10),
                 pk INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                 idx INT GENERATED ALWAYS AS (CASE WHEN c1 IS NULL THEN pk ELSE NULL END));
  CREATE UNIQUE INDEX idx ON T(c1, idx);

Here is a quick test for this last example:

db2 => insert into t(c1) values 1, 2, 3, 4, 5, NULL;
DB20000I The SQL command completed successfully.
 
db2 => insert into t(c1) values NULL;
DB20000I The SQL command completed successfully.
 
db2 => insert into t(c1) values 1;
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 "2" constrains table "SERGE.T" from having duplicate values 
for the index key.
SQLSTATE=23505
 
db2 => select * from t;

C1         PK          IDX
---------- ----------- -----------
1          1           -
2          2           -
3          3           -
4          4           -
5          5           -
-          6           6
-          7           7
 
7 record(s) selected.

#Db2