Hi All,
I am working with db2 v8.2 aix 5.2.

Recently I be forced to change some unique index to primary index (I am using insert_update cluse in my import statement) , I want to know what is different between unique index and primary index. can we have null value in unique index? I know we can not have null value in primary index.

thank you in advance for your help.

If you want to have nulls in a unique index, use the "WHERE NOT NULL" clause in your create index statement.

Technically there's no such thing as a "primary index". However, in DB2, you have to have a unique index to guarantee the uniqueness of a primary key. When you define a primary key, DB2 LUW checks to see if an appropriate unique index exists. If one doesn't then DB2 creates a suitable index. If a suitable unique index exists then DB2 uses it.


James Campbell

We only define a primary key if we are going to use the that table as the parent in an RI rule.

WHERE NOT NULL is an option for create index on DB2 for OS/390 and z/OS (maybe DB2 for I-series ??), but is not available on DB2 for LUW.

You can have a null in a unique index on DB2 for LUW (assuming the column is nullable), but you can only have one of them.

Interesting! So if you want to enforce an optional, non-identifying, one-to-one foreign key relationship on LUW, would it require triggers?

Triggers are one way to that (which I have seen done by one of my customers).

Another way is to create a new parent table that contain the unique values that are not null and establish a RI constraint. RI constraints will allow nulls in the child table (foreign key).

I feel sorry for the organization that pays your salary.

The creation of a parent table and RI constraint will enforce the optional one-to-many relationship but you would need a UNIQUE WHERE NOT NULL or a trigger to enforce the optional one-to-one.