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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 30 Jun 2006 13:01:11 +0100
Message-ID: <7765c8970606300501q7402d029vba5ca54b9e48a523@mail.gmail.com>

Intended to cc the list

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

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 Received on Fri Jun 30 2006 - 07:01:11 CDT