 | | | about "Oracle recommends that unique indexes be created explicitly " | about "Oracle recommends that unique indexes be created explicitly " 2006-06-30 - By Niall Litchfield
Back
Intended to cc the list
-- ---- -- Forwarded message -- ---- --
From: Niall Litchfield <niall.litchfield@(protected)>
Date: Jun 30, 2006 1:00 PM
Subject: Re: about "Oracle recommends that unique indexes be created explicitly"
To: wqhhp@(protected)
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 (See http://ORA-00904.ora-code.com): : 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
|
|
|