Fwd: about "Oracle recommends that unique indexes be created explicitly"

Intended to cc the list

---------- Forwarded message ----------
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
Date: Jun 30, 2006 1:00 PM
Subject: Re: about "Oracle recommends that unique indexes be created explicitly"
To: wqhhp@xxxxxxxxx


There are , at least, two situations to which the guide is referring
demonstrated below. The first is the case where a suitable index
already exists and the second is the case when the constraint is
created as initially deferred.

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

NIALL @ nl102 >create table tab1(id number, description varchar2(10));

Table created.

NIALL @ nl102 >create table tab2(id number , description varchar2(10));

Table created.

NIALL @ nl102 >create index i1 on tab1(id);

Index created.

NIALL @ nl102 >alter table tab1 add constraint pk_tab1(id);
alter table tab1 add constraint pk_tab1(id)
                                     *
ERROR at line 1:
ORA-00904: : invalid identifier


NIALL @ nl102 >alter table tab1 add constraint pk_tab1 primary key(id);

Table altered.

NIALL @ nl102 >alter table tab2 add constraint pk_tab2 primary key(id)
initially deferred;

Table altered.

NIALL @ nl102 >select index_name,uniqueness from user_indexes
2  where table_name in ('TAB1','TAB2');

INDEX_NAME                     UNIQUENES
------------------------------ ---------
PK_TAB2                        NONUNIQUE
I1                             NONUNIQUE

2 rows selected.

NIALL @ nl102 >

regards

--
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l


Other related posts: