"Vincent Ventrone" <vav_at_brandeis.edu> wrote in message news:<9lj8di$mce$1_at_new-news.cc.brandeis.edu>...
> > I don't know the difference of 'using index' clause and just primary
> > key constraint...
>
> You will get a unique index in either case since Oracle uses a unique index
> to enforce a PK constraint -- the diff. is that if you include the USING
> INDEX clause you can name the index (otherwise it will have a db-generated
> name) & you can include a storage clause -- otherwise the index will end up
> in the owning schema's default tablespace & the storage parameters will be
> the tablespace defaults. I strongly recommned that include an explicit
> USING INDEX clause...
Vincent, since version 8 Oracle has had the ability to use an
non-unique index to support a PK constraint. I believe that you need
to use a non-unique index for defferred constraints to work otherwise
the unique index logic prevents you from inserting the duplicate key,
which deferred constraints allow up to the time of commit. Also if
you have multiple columns on the table that contain the PK key columns
and you rebuild the index and then try to add the primary key via an
'alter table' statement Oracle will use the existing index to support
the PK rather than build one using the storage clause you gave it.
The constraint name will be what you told Oracle to use, but the index
used to support the index will not be the one you expected.
Received on Fri Aug 17 2001 - 12:35:53 CDT
