Re: non unique Index and Primary Key on same column. Is it needed?
- From: Mark Richard <mrichard@xxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 Aug 2004 15:31:17 +1000
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 //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
-----------------------------------------------------------------
Other related posts:
