Hi all,

Is it proper to create an unique index to serve the purpose of
a PK? If so, why is that so and what's the advantage/disadvantage between the 2 options?

Thanks
ME

If you create a PK, DB2 will see if there is a unique index on the key already created. If not, DB2 will create the unique index using its own naming convention. If the PK is dropped, the DB2 created unique index will also be dropped.

If you create your own unique index before defining the PK (with alter table), then you can choose the index name. Such an index will not be dropped if the PK is dropped.

If you want to create a foreign key relationship on another table that points to the primary key on the parent table, then you would probably want to explicitly define the primary key (with either a DB2 created unique index or a DBA created unique index). If no foreign keys will be defined, then a DBA created unique index without a PK serves the same purpose.

Hi Marc,

So, is it appropreiate to say that PK in DB2 is actually implemented by
an unique index?

ME

Yes. But I believe that if you try to insert a duplicate row, you will get a different error message depending on whether a PK or just a unique index is defined.

As I understand it, a Primary Key is simply a special case
of a unique index, you can have many unique indexes
on a table, if they specify different condition, but you can
only have one (1!) primary key.

Thus, when creating a primay key, you are implicitly
creating a unique index, but not vice versa.

BOW

Correct... it's a database design thing more than anything. A table can have many candidate keys, the primary key is the one designated to identify a row in the table.

For instance - when I was building my house, the builder had a unique lot number assigned to it, and then I also had a street address. Both uniquely identify my house, but only one can be the primary key. The other would certainly be a candidate key, and may even function in a very similar manner.

One thing to remember is that Referential Integrity was not in the original DB2 specification. It was introduced about 1988 for DB2 OS/390 (MVS back then). Prior to that, we just created a unique index on the parent table and had the application validate undeclared “referential constraints” via program code when updating the dependent table. This is still an option that performs quite well if you trust the application developers to do it correctly.

More recently, UNIQUE constraints have been introduced.

If all these concepts had been available from the beginning, it might have been implemented a little differently via DDL to make things more clear.

There is also a subtle difference.
You can have one NULL value in the unique index column ,but no two NULLS,because two NULLs are not unique.

If you want to define primary key on a column then thus column must be defined NOT NULL,so when you define a primary key it uses two constraints NOL NULL and UNIQUE.

so if a column has unique index,but has one null value you can not define primary key on that one.

How DB2 uses unique index to support primary key constraint is also a very interesting topic, totally depends upon the index structure.

Infact in Oracle a non-unique index can be used as primary key constraint , interesting!!!!


Regards,

Mujeeb