| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: orace sql query
On Jun 23, 6:18 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > I am now a bit confused on this topic, or are we reading things
> > differently?
>
> > Create a generic table without a primary key:
> > CREATE TABLE T1 (
> > C1 VARCHAR2(20),
> > C2 VARCHAR2(20));
>
> > Table created.
>
> > Create a unique index on the C1 column of the table:
> > CREATE UNIQUE INDEX IND_T1 ON T1(C1);
>
> > Index created.
>
> > Specify that column C1 is the primary key for T1:
> > ALTER TABLE T1 MODIFY (PRIMARY KEY (C1));
>
> > Table altered.
>
> > Take a look at the indexes for the table:
> > SELECT
> > INDEX_NAME
> > FROM
> > DBA_INDEXES
> > WHERE
> > TABLE_NAME='T1';
>
> > INDEX_NAME
> > ----------
> > IND_T1
>
> > DROP INDEX IND_T1;
>
> > ERROR at line 1:
> > ORA-02429: cannot drop index used for enforcement of unique/primary
> > key
>
> > The above seems to be in agreement with the posts by David
> > Fitzjarrell, Ed Stevens, Frank van Bortel, and the Oracle
> > documentation. Or, is this not a valid test?
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> I know what the error message says.
>
> And I know what the document Frank posted the link to says.
>
> And until corrected by someone I stand by what I wrote.
>
> Consider this:
>
> SQL> select constraint_name, constraint_type, deferrable
> 2 from user_constraints
> 3 where table_name = 'UCLASS';
>
> CONSTRAINT_NAME C DEFERRABLE
> ------------------------------ - --------------
> PK_UCLASS P DEFERRABLE
> CC_UCLASS_CLASS_NAME C NOT DEFERRABLE
>
> SQL> SELECT index_name, index_type, uniqueness
> 2 FROM user_indexes
> 3 WHERE table_name = 'UCLASS';
>
> INDEX_NAME INDEX_TYPE UNIQUENES
> ------------------------------ --------------------------- ---------
> PK_UCLASS NORMAL NONUNIQUE
>
> SQL> DROP INDEX pk_uclass;
> DROP INDEX pk_uclass
> *
> ERROR at line 1:
> ORA-02429: cannot drop index used for enforcement of unique/primary key
>
> I don't believe a non-unique index is being used for enforcement of
> a unique condition. Any comment Tom? Jonathan? Richard? Howard? Except
> to speed the up the search.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
I happen to have a copy of Tom Kyte's "Expert Oracle One on One" on hand - in electronic form of course, because it is just about impossible to find items of interest using the book's index (his later book improved much of this problem). I will quote a couple items from the book - even though the book is a bit old, I suspect that the information is still mostly correct.
Page 644:
"If you try to trick Oracle by realizing that a primary key can be
enforced by a non-unique index as well
as a unique index, you'll find that it will not work either" - this is
in a section of the book that discusses partitioning.
Page 355: (looks somewhat similar to the test that I posted)
tkyte_at_TKYTE816> create index t_idx on t(x,y); Index created.
tkyte_at_TKYTE816> alter table t add constraint t_pk primary key(x); Table altered.
tkyte_at_TKYTE816> select object_type, object_name,
2 decode(status,'INVALID','*','') status,
3 tablespace_name
4 from user_objects a, user_segments b
5 where a.object_name = b.segment_name (+)
6 order by object_type, object_name
7 /
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ---------------------INDEX T_IDX DATA
tkyte_at_TKYTE816> drop index t_idx;
drop index t_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary
key
Page 643:
"In order to enforce uniqueness, and that includes a UNIQUE constraint
or PRIMARY KEY constraints,
your partitioning key must be included in the constraint itself. This
is the largest impact of a local index,
in my opinion. Oracle only enforces uniqueness within an index
partition, never across partitions. What
this implies, for example, is that you cannot range partition on a
TIMESTAMP field, and have a primary
key on the ID which is enforced using a locally partitioned index.
Oracle will utilize a single global
index to enforce uniqueness."
Page 645:
"Following on from our previous example, here is a quick example of
the use of a global index. It shows
that a global partitioned index can be used to enforce uniqueness for
a primary key, so you can have
partitioned indexes that enforce uniqueness"
I believe in Tom Kyte's "Expert Oracle Database Architecture" he mentions that a non-unique index can be used to enforce the primary key constraint, as Oracle only needs to test for the non-existence of a matching value to verify that the primary key will not be violated.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat Jun 23 2007 - 20:43:15 CDT
 |
 |