?
Solved

Oracle Primary Key, Unique Key, Unique Index

Posted on 2011-05-09
12
Medium Priority
?
850 Views
Last Modified: 2012-05-11
I assume he rationale for defining a Unique Index vs Primary Key or Unique Key is the Unique Index allows column values of NULL.

What are the reasons for defining a Unique Key with or without defining matching Primary Key columns? Or defining both with some or all different columns?

I am to update an existing packages that updates Oracle table data based on unique columns only. This package treats tables with Primary Key vs Unique Key completely differently, and I cannot see a reason for this different treatment.

Thanks,
Bill
0
Comment
Question by:pendlewe
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 100 total points
ID: 35721826
Your first rationale is kind of correct: A Primary Key  implements both unique and not null constraints. Therefore, reasons for defining a column or set of columns unique but not primary key could be:
- You want to allow the columns to accept nulls, which is not valid for primary key;
- The unique constraint will be applied in fields that are not the primary key.
0
 

Author Comment

by:pendlewe
ID: 35722002
I think the question becomes why define a Unique Key with columns that are, or are not, defined in the Primary Key? Why have both, or why have a Unique Key instead of a Primary Key?  Is there any  difference in how Oracle will process these unique constraints? Does a Unique Key not implement not null constraints? The way the existing package handles Primary Key vs Unique Key is complex, and I cannot see any reason to handle Primary Key columns differently than Unique Key columns.

Thanks,
Bill
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 35722147
Don't think of the unique one as a 'key'.  Think of it in terms of what it is, an index.

I'm not sure I can explain it any better than what has already bee said:  A unique index (UI) will allow a NULL.  A PK will not.  It's that simple.

A table will typically have a PK as part of the design in a normalized relation design.  The PK should be the PK per the rules of normalization.  You should not add additional columns to the PK just to enforce a not null constraint.

You can only have one PK per table.  You can have many UIs.

You might have two columns in a table that aren't even part of the PK that must be unique when they both have values.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 200 total points
ID: 35722174
>I assume he rationale for defining a Unique Index vs Primary Key or Unique Key is the Unique Index allows column values of NULL.

Hardly.  The real question is: "why does a unique key permit nulls"


Every table should have a primary key.  It's purpose is to assure that rows are uniquely identifiable. As you mentioned, it also enforces the not null constraint on every column, thus assuring 1st normal form.

But what if there are other business rules the demand another set of columns is unique?  Since there can only be 1 primary key, the unique key is the mechansim to enforce those rules.  Honestly, in a world where database designers are far too quick to use surrogate keys, they are all too often negligent about then declaring the unique keys that promote the business rules about uniqueness.  Data quality suffers.

Unique keys are also useful as the target of a foreign key reference when it is desirable to propogate the unique key rather than the primary key.

I have never heard of unique key being used instead of primary key in order to allows nulls in the key and I do not advise it.  

I don't know why your package would treat unique keys differently.




 

0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 100 total points
ID: 35722934
Hi,

A primary key is:
 - a unique key
 - that has all columns NOT NULL
 - and ... that is the 'primary' key (and you can consider other unique constraints as secondary keys if you want): you can only have one.

What is different ? If you want the table to be implemented as an IOT (index organized table) it will be physically ordered by the primary key. Besides that, primary key and unique + not null are similar: use can reference both with foreign keys.

I see no reason to define a column both in primary key constraint and in unique constraint.

I don't know your package, but I see no reason for handling differently primary keys and unique constraints: you can update a single row using one or the other.

Regards,
Franck.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 200 total points
ID: 35723401
>I see no reason to define a column both in primary key constraint and in unique constraint.

Sometimes there are reasons.  For example, maybe you want both both of these combinations to be unique:

tblScheduledExam
PK: StudentID, ExamID  (student not allowed to take the same exam twice)
UK: StudentID, ExamDay (student not allowed to take two exams at the same day)





0
 

Author Comment

by:pendlewe
ID: 35728317
Thank you all, I am much clearer now on this issue. Either the original architect erred, or subsequent revisions were erroneously made.

Best Regards,
Bill
0
 

Author Closing Comment

by:pendlewe
ID: 35728341
With so many accurate responses, and my level of confusion, I apologize that I cannot select a single answer as best.

Best Regards,
Bill
0
 
LVL 22

Expert Comment

by:dportas
ID: 35731808
>> slightwvDate: "Don't think of the unique one as a 'key'.  Think of it in terms of what it is, an index."

I would strongly suggest exactly the opposite. Key constraints are keys, whether they happen to be enforced by a UNIQUE constraint or a PRIMARY KEY constraint. Their purpose is to ensure the uniqueness of the columns that are part of that constraint. Indexes are something different. They are a performance optimisation feature and have nothing much to do with keys per se.

Keys don't allow nulls. A column that permits nulls isn't part of any key so it is incorrect to refer to nullable columns as a "key" or part of a key even if they have a UNIQUE constraint on them.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35731890
umm.... didn't you just say the same thing I did?

I was suggesting not using the term 'unique key' as a unique constraint/index for the very reason you just gave.  If you think 'key' = 'not null' and 'unique' allows null, pendlewe might be able to see the difference between the terms.
0
 

Author Comment

by:pendlewe
ID: 35731900
Thanks,
Bill
0
 
LVL 22

Expert Comment

by:dportas
ID: 35800680
slightwv: "umm.... didn't you just say the same thing I did?"

No because the column(s) included in a UNIQUE constraint can be a key too if they are defined as NOT NULL. If the columns in question are key then you should call them key, no matter whether they are defined by a PRIMARY KEY or UNIQUE constraint. Just avoid the term "unique key" because it's a potentially misleading tautology.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question