Are there any reason to use both primary key and unique key together on the same field?

Question

I am analyzing an Oracle database design and I am perplexed at seeing both unique keys and primary keys on the same fields. These unique-primary key pairs are consistently created on all tables. I see no reason to do this.

If I have a primary key anyway, is there a good reason to create an additional unique key on the same field?

Answer 1

For a table resolving a many-to-many it would be common to have a two part key (as indicated by Quassnoi). It is also quite likely to need indexes supporting access through either parent.

If you have, for example, PERSON, ADDRESS and PERSON_ADDRESS tables, your PERSON_ADDRESS table may have the primary key of (PERSON_ID, ADDRESS_ID) and a supporting index. You would also have another index on (ADDRESS_ID,PERSON_ID), and you would likely make this a UNIQUE index (as it is a unique combination of fields).

It is also possible that your DBA has some particular way of generating tables that starts with a UNIQUE index on the primary key fields followed by the creation of the PRIMARY KEY constraint. That may show up in some GUI tools in the way you suggest.