| 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?
Mark,
I do recall something about that, but, I believe, if the index is unique, then if you disable the constraint, the index is dropped, presumably because by disabling a unique constraint, you no longer want that constraint imposed, and the only way to ensure it's not=20 is to drop the unique index. If the index supporting the PK (or UK) is non-unique, then it's not dropped.
I believe that at least was true in 8i. I believe that starting in 9i, there is a "KEEP INDEX" syntax, which I don't have handy at the moment. =20
Still awake....can't sleep.....
Yawn.....
-Mark
-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Mark Richard Sent: Tue 8/24/2004 1:31 AM To: oracle-l_at_freelists.org Cc:=09 Subject: 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.
=
=20
nn20002_at_netscape.n =
=20
et To: =
oracle-l_at_freelists.org =
=20
Sent by: cc: =
=20
oracle-l-bounce_at_fr Subject: non unique =
Index and Primary Key on same column. Is it needed? =20
eelists.org =
=20
=
=20
=
=20
24/08/2004 14:00 =
=20
Please respond to =
=20
oracle-l =
=20
=
=20
=
=20
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 ----------------------------------------------------------------- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>= >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 01:16:05 CDT
 |
 |