CODEWITHSUNDEEP
oracle-database
Acamboos Acambo
Acamboos Acambo

Reputation: 91

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

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?

Upvotes: 9

Views: 4290

Answers (5)

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 4

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52157

Just a little theoretical background here... When modeling your table, you identify a set of keys. These keys are logically equivalent, but for practical purposes you pick one of them and call it "primary" while the rest of them become "alternate".

(In DDL SQL, a primary key is called "PRIMARY KEY", while "alternate key" is called "UNIQUE constraint".)

So, in light of that, your question is equivalent to: "is there a good reason to have two identical keys", and the answer is: "no".

That being said, you may have overlapping keys (i.e. keys that share some fields but not all), but this is usually a sign of a bad design... and the answer is: "probably not".

OTOH, if by "unique key", you actually mean "unique index", then yes, you need both of them.

Index is not a logical constraint - it is there just to allow a logical constraint such as PRIMARY KEY to perform well (and for querying, but that's a different topic).

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425813

Oracle won't let you create multiple UNIQUE and PRIMARY KEY constraints on the same field set in the same order and will fail with ORA-02261.

If you have composite keys, you can create PRIMARY KEY on the column set in one order (PRIMARY KEY (a, b)) and a unique constraint on another (UNIQUE (b, a)).

This will parse and execute, however a single index will be used to police both constraints so it makes no sense.

Could you please post the table scripts?

Upvotes: 1

a1ex07
a1ex07

Reputation: 37382

I believe it's impossible (PK and unique constraint on the same column[s])...

You cannot designate the same column or combination of columns as both a primary key and a unique key.

(from here, section "Restrictions on Primary Key Constraints"). Isn't it?

Upvotes: 1

Davide Piras
Davide Piras

Reputation: 44595

No, there is no reason to have it also as unique; when you set a column as PK you are sure that:

  1. No NULL will be accepted for that column on INSERT or UPDATE;
  2. Values in the whole table for that column are always UNIQUE;

so just PK is enough. Since there is a UNIQUE index for the PK column, by definition, there is no need to add any other index on that column only because queries will use the PK index whenever only that column is affected.

Upvotes: 1

Related Questions