Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
about "Oracle recommends that unique indexes be created explicitly "

about "Oracle recommends that unique indexes be created explicitly "

2006-06-30       - By Niall Litchfield

Reply:     1     2  

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 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