Re: orace sql query

On Jun 23, 6:18 pm, DA Morgan <damor...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx (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@TKYTE816> create table t ( x int, y int );
Table created.

tkyte@TKYTE816> create index t_idx on t(x,y);
Index created.

tkyte@TKYTE816> alter table t add constraint t_pk primary key(x);
Table altered.

tkyte@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
TABLE T DATA

Here, Oracle will use the index T_IDX to enforce the primary key. We
can see this clearly if we try to
drop it:

tkyte@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.

.

Relevant Pages

  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... believe otherwise but were that not true you could not enforce ... Puget Sound Oracle Users Groupwww.psoug.org ... ALTER TABLE T1 MODIFY (PRIMARY KEY (C1)); ... SQL> select constraint_name, constraint_type, deferrable ...
    (comp.databases.oracle.server)
  • how can I determine the primary key for a table?
    ... I have a set of oracle tables which were set up by people no longer ... available for consultation. ... SQL> select index_name, uniqueness ... How can I determine what column in TABLE_H is the primary key? ...
    (comp.databases.oracle.misc)
  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ...
    (comp.databases.oracle.server)
  • Re: Autonumber Fields
    ... What would you do to guarantee uniqueness in a Contacts table or some such ... I have a database that includes an Employees table. ... > Clustered Index can only be a Primary Key. ... >> Suppose I wanted to create a clustered index in an Access table. ...
    (microsoft.public.access.tablesdbdesign)