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 00:39:35 -0400

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@xxxxxxxxxxxxx on behalf of nn20002@xxxxxxxxxxxx
Sent:   Tue 8/24/2004 12:00 AM
To:     oracle-l@xxxxxxxxxxxxx
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@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: