| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_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
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
-- 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
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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 -----------------------------------------------------------------
 |
 |