Re: Help: Confused with Primary Key and Index

"Lothar Armbrüster" <la_at_oktagramm.de> wrote in message news:PM0003801BDF778B26_at_hades.unknown.dom...
> u132781982_at_spawnkill.ip-mobilphone.net wrote:
> > Sorry if my question sounds stupid,
> > when you create a primary key, does
> > oracle always create an index associate
> > with that primary key? Will this index
> > have the same name as your primary key?
> >
> Yes, in the general case (i.e. disregarding some specialties like index
> organized tables) Oracle enforces primary keys with a unique index.

Not true. Oracle enforces primary keys with an index, which may *or may not* be unique. If you choose to create your own non-unique index on a field which is destined to become the primary key, then when you eventually declare the primary key constraint, the non-unique index will be used.

Even if you don't pre-create the index, it's possible in 8.0 and above to declare the primary key constraint 'deferrable', at which point Oracle will do the automatic index creation -and will do so by creating a non-unique index.

> When you create the primary key as a constraint with a given name the
> index gets this name:
>

Again, only true under certain circumstances. You can use the 'using index' clause to specify any name you desire. The index only uses the constraint name if you don't otherwise specify.

All of this also holds true for unique constraints.

IMHO, all Primary and Unique Key constraints should be created 'deferrable' so that non-unique indexes are created to enforce them. That way, they don't get dropped automatically when you temporarily disable the constraint.

Regards
HJR
> alter table my_tab add constraint pk_my_tab primary key(my_id);
>
> If you don't give your constaint a name you get a system generated one
> and the index in namaed after the constraint (AFAIK).
> If you specify the primary key constraint as a column constraint you get
> a system generated name too:
>
> creste table my_tab (my_id number(5) primary key);
>
> > If the table is partitioned, can we also
> > partitoned the index as well? How can you
> > know this index is from primary key or is
> > a pure index?
>
> Yes, you can create partitioned indexes. The index partitions get the
> same partition scheme as the table. I think, you have to add the
> partition key as the first indexed column to archieve this, but I'm nut
> quite sure about this.
> Have a look at user_ind_partitions and user_partition_indexes to find
> out about partitioned indexes.
> I also remember weakly about local and global indexes in that context,
> but you will have to look in the docs to get more info on this.
>
>
> Hope that helps,
> Lothar
>
> --
> Lothar Armbrüster | la_at_oktagramm.de
> Hauptstr. 26 | la_at_heptagramm.de
> D-65346 Eltville | lothar.armbruester_at_t-online.de
Received on Fri Mar 23 2001 - 09:32:34 CET