RE: non unique Index and Primary Key on same column. Is it needed?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Aug 2004 02:20:24 -0400
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@xxxxxxxxxxxxx on behalf of Mark Richard
Sent: Tue 8/24/2004 1:31 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx =
=20
et To: =
oracle-l@xxxxxxxxxxxxx =
=20
Sent by: cc: =
=20
oracle-l-bounce@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
__________________________________________________________________
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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
