RE: Primary Constraints and Indexes

you cannot create 2 indexes with the same columns on a table. You can either create it thru primary key or explictly but not both.

Disabling/dropping primary and unique constraints on a table also dropped the indexes.

Arvind Aggarwal

• Shakeel Qureshi <msklq_at_yahoo.com> wrote:
  > When you declare pk or uk constraint at the time of
  > table creation the system automatically creates
  > an index when the table is created. However, if you
  > add with the constraint syntax like "using index
  > tablespace", it means that the system generated
  > index
  > should be created on tablespace named INDEX, which
  > is
  > equally good like any other index for data search.
  > However, if you want you can drop this index and
  > create a new one with your own naming conventions
  > and
  > storage parameters.
  >
  > Hope this clarifies the process.
  >
  > Regards,
  >
  > Shakeel Qureshi
  > DBA (Oracle Certified)
  > squeshi_at_barpoint.com
  >
  >
  > --- Kevin Kostyszyn <kevin_at_dulcian.com> wrote:
  > > Why are you creating the PK like that? Create the
  > > PK, then create the index
  > > after the table is created.
  > > Wouldn't that accomplish what you want?
  > > Kev
  > >
  > > -----Original Message-----
  > > McGill
  > > Sent: Wednesday, January 10, 2001 10:46 AM
  > > To: Multiple recipients of list ORACLE-L
  > >
  > >
  > > As a part-time dba, I am confused about the
  > > differences between
  > > Primary Keys and Indexes.
  > >
  > > For example if I create a table "EMPLOYEES" and in
  > > the table
  > > creation statement I do something like:
  > > create table ...
  > > constraint XXX primary key (EMP_ID) using
  > > index ...
  > > then later I create an index:
  > > create index YYY on EMPLOYEE (EMP_ID) ...
  > > I know I have created two indexes (the first one
  > > system-named
  > > and implicitly created and the second name YYY and
  > > created
  > > explicitly) but do I really need the second index
  > > YYY?
  > > It seems to me the YYY index is not really needed?
  > > Can
  > > someone shed some light on this for me.
  > >
  > > THANKS, Wayne
  > > McGillWL_at_navair.navy.mil
  > >
  > > --
  > > Please see the official ORACLE-L FAQ:
  > > http://www.orafaq.com
  > > --
  > > Author: Wayne McGill
  > > INET: McGillWL_at_navair.navy.mil
  > >
  > > Fat City Network Services -- (858) 538-5051
  > FAX:
  > > (858) 538-5051
  > > San Diego, California -- Public Internet
  > > access / Mailing Lists
  > >
  >

  ---

  
  > > To REMOVE yourself from this mailing list, send an
  > > E-Mail message
  > > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > > 'ListGuru') and in
  > > the message BODY, include a line containing: UNSUB
  > > ORACLE-L
  > > (or the name of mailing list you want to be
  > removed
  > > from). You may
  > > also send the HELP command for other information
  > > (like subscribing).
  > >
  > > --
  > > Please see the official ORACLE-L FAQ:
  > > http://www.orafaq.com
  > > --
  > > Author: Kevin Kostyszyn
  > > INET: kevin_at_dulcian.com
  > >
  > > Fat City Network Services -- (858) 538-5051
  > FAX:
  > > (858) 538-5051
  > > San Diego, California -- Public Internet
  > > access / Mailing Lists
  > >
  >

  ---

  
  > > To REMOVE yourself from this mailing list, send an
  > > E-Mail message
  > > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > > 'ListGuru') and in
  > > the message BODY, include a line containing: UNSUB
  > > ORACLE-L
  > > (or the name of mailing list you want to be
  > removed
  > > from). You may
  > > also send the HELP command for other information
  > > (like subscribing).
  >
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Yahoo! Photos - Share your holiday photos online!
  > http://photos.yahoo.com/
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Shakeel Qureshi
  > INET: msklq_at_yahoo.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).