Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: non unique Index and Primary Key on same column. Is it needed?

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

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Tue, 24 Aug 2004 15:31:17 +1000
Message-ID: <OF2D3DE452.EF6F0A95-ONCA256EFA.001DD70C@transurban.com.au>

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_at_netscape.n                                                                                             
                      et                        To:       oracle-l_at_freelists.org                                                     
                      Sent by:                  cc:                                                                                  
                      oracle-l-bounce_at_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_at_freelists.org 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_at_freelists.org 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 -----------------------------------------------------------------
Received on Tue Aug 24 2004 - 00:26:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US