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

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 24 Aug 2004 00:39:35 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB22@bosmail00.bos.il.pqe>

Nagarajan,

At primary key creation time, if an appropriate column already exists, = Oracle
will utilize it, rather than creating a redundant column.

In fact, note that a non-unique index may be used to enforce a primary or unique key constraint. The index need not be unique.

In cases where Oracle does generate an index automatically, you will = have
a name of the form 'SYS_Cxxxxxxx', where xxxxxx is a number.

Hope that helps,

-Mark

-----Original Message-----

From:	oracle-l-bounce_at_freelists.org on behalf of nn20002_at_netscape.net
Sent:	Tue 8/24/2004 12:00 AM
To:	oracle-l_at_freelists.org
Cc:=09
Subject:	non unique Index and Primary Key on same column. Is it needed?

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=20
     =20

);

=20
CREATE INDEX MACHINE_INDEX ON MACHINE(
MACHINE_KEY
);

   =20
ALTER TABLE MACHINE
    ADD PRIMARY KEY ( MACHINE_KEY );
     =20
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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Mon Aug 23 2004 - 23:35:20 CDT