Re: non unique Index and Primary Key on same column. Is it needed?




In addition to Mark Bobak's reply...

I believe there is a subtle difference (or used to be) when it comes to
dropping the primary key.  Having the index defined manually means Oracle
won't removing the index when dropping the primary key.  If you perform a
lot of DDL on the database (ie: dropping primary keys when loading batches
of data overnight) this may be significant.  I think newer versions of
Oracle have additional syntax when dropping constraints to either keep or
remove the index.

I'm positive this has been discussed before in detail - you might want to
try searching the archives (even via google if necessary).  The previous
discussion will have more details and be more correct than myself since I'm
working by memory.  You could also search the Oracle doco for syntax
options in "alter table ... drop constraint" - I think it might show the
syntax for keeping indexes in Oracle 9i and later (I only have Oracle 8i
doco handy).

Regards,
      Mark.



                                                                                
                                                     
                      nn20002@xxxxxxxxxx                                        
                                                     
                      et                        To:       
oracle-l@xxxxxxxxxxxxx                                                     
                      Sent by:                  cc:                             
                                                     
                      oracle-l-bounce@fr        Subject:  non unique Index and 
Primary Key on same column. Is it needed?             
                      eelists.org                                               
                                                     
                                                                                
                                                     
                                                                                
                                                     
                      24/08/2004 14:00                                          
                                                     
                      Please respond to                                         
                                                     
                      oracle-l                                                  
                                                     
                                                                                
                                                     
                                                                                
                                                     




Hi ,

I saw a script creating a table and then an index on one of the column. But
after that they alter the table to create an Primary Key on the same
column.
Why is that needed. I am of the assumption that when you create a primary
key, the system automatically creates an index to enforce it. The same key
could be used as an index also. Then why they create an extra index.
example

CREATE TABLE MACHINE(
        MACHINE_KEY INTEGER  NOT NULL ,
        USABILITY_SETTINGS_KEY INTEGER  NULL ,
        MACHINE_ID VARCHAR2(256)  NULL ,
        NAME VARCHAR2(50)  NULL ,
        STATUS VARCHAR2(25)  NULL

);

CREATE INDEX MACHINE_INDEX ON MACHINE(
        MACHINE_KEY
);

ALTER TABLE MACHINE
    ADD PRIMARY KEY ( MACHINE_KEY );

I would appreciate if you could help me. Sorry if it is a silly question.
Thanks
nagarajan


__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at
http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------





<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for 
delivery of the message to such person), you may not copy or deliver this 
message to anyone.
In such a case, you should destroy this message and kindly notify the sender by 
reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet 
e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate 
to the official business of Transurban Infrastructure Developments Limited and 
CityLink Melbourne Limited shall be understood as neither given nor endorsed by 
them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: