Re: Unique and non unique index

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 15 Mar 2006 08:57:07 -0800
Message-ID: <1142441806.710058@yasure.drizzle.com>

Muthu wrote:
> No the primary key index will always create the unique index.
> The Non unique index are required to be created for faster data
> retrieval and not for maintaining data integrity.

Your information is incorrect.

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 15 08:52:21 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create table t (

2 col1 NUMBER(3),
3 col2 VARCHAR2(10));

Table created.

SQL> ALTER TABLE t

   2 ADD CONSTRAINT pk_t
   3 PRIMARY KEY (col1)
   4 INITIALLY DEFERRED DEFERRABLE
   5 USING INDEX Table altered.

SQL> SELECT index_name, index_type, uniqueness

2 FROM user_indexes

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
PK_T                           NORMAL                      NONUNIQUE

Oracle can use either unique or non-unique indexes to support a primary key constraint.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Wed Mar 15 2006 - 10:57:07 CST