Re: Unique and non unique index

Rama Shankar wrote:
Hi All,
We can enforce primary key constraint by using both unique
and non-unique index. I want to know when to use unique index and when
non unique.

If you want to enforce a primary key constraint, create a primary key for the table. Oracle will take care of creating the associated index automagically.

If some column other than the PK needs to be unique, create a unique *constraint*, not a unique index. Again, Oracle will take care of creating the associated index automagically.

You can use a unique index to enforce uniqueness but this is sub-optimal. Among other things you can foreign key to a column that has a unique constraint, but you can't foreign key to a column that has it's uniqueness enforced by an index.

If you need an index for performance reasons, create a non-unique index.

//Walt
.

Relevant Pages

  • Re: Unique
    ... A primary key by definition is required and must be unique, ... just PRIMARY KEY will enforce both those requirements, ... INT by itself doesn't guarantee an auto-incrementing number. ... just as adding PRIMARY KEY adds a primary key constraint ...
    (microsoft.public.sqlserver.programming)
  • Re: orace sql query
    ... Puget Sound Oracle Users Groupwww.psoug.org ... "If you try to trick Oracle by realizing that a primary key can be ... Oracle will use the index T_IDX to enforce the primary key. ... "In order to enforce uniqueness, and that includes a UNIQUE constraint ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ...
    (comp.databases.oracle.server)
  • Re: Primary Keys & Relationships
    ... If you don't intend to enforce RI, ... you will be presented with the options to Cascade Delete and Cascade Update. ... such as Order - OrderDetails, the OrderDetails rows have no meaning except ... Many people prefer to use autonumbers as their primary key when the natural ...
    (microsoft.public.access.tablesdbdesign)