Oracle Unique Index without matching Primary Key or Unique Key

When a Oracle Unique Index is defined for a table, and no Primary Key or Unique Key is defined for the table (Or a Primary Key or Unique Key is defined that has less columns than the Unique Index) Is the unique index enforced for table updates and inserts for Oracle 11 and/or previous releases?
pendleweAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>In older Oracle, a Unique Index was created (*I think*) whenever a Primary Key was created.

I forget a lot about the very early versions of Oracle but there is a difference between a PK and Unique index.

A PK will NOT allow a null where a Unique index will.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Not sure about the rest of the question but...

>>Is the unique index enforced for table updates and inserts for Oracle 11 and/or previous releases?

Yes.  That's sort of by definition of an RDBMS.


0
 
pendleweAuthor Commented:
In older Oracle, a Unique Index was created (*I think*) whenever a Primary Key was created. I think this is no longer true. My objective is to automate insert, update, and deletes in ~1000 oracle tables based on uniqueness, using dynamic SQL.

Thanks,
Bill
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberConnect With a Mentor Commented:
if an index (unique or non-unique) is already available that Oracle thinks will be efficiently usable to enforce the PK constraint,  then it won't create a new index.  
If there is no such index in place then creating the constraint will create the index.  This is still true all the way up to 11gR2.




0
 
sdstuberConnect With a Mentor Commented:
so, obvious question is: Why would an index NOT be usable?

If it's for different columns than the PK enforces
or if it has insufficient columns

0
 
pendleweAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.