| 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, 2:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
> fitzjarr..._at_cox.net wrote:
> >>From the documentation:
>
> > "Oracle enforces all PRIMARY KEY constraints using indexes.
>
> To the best of my knowledge the document is incorrect.
>
> The index is used to speed the search for a duplicate value ... not
> to enforce it.
>
> I will gladly stand corrected on this if someone has reason to
> believe otherwise but were that not true you could not enforce
> uniqueness with a non-unique index.
>
> I didn't find the support I wanted in the time I have available
> right now but let me quote from Tom Kyte:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3...
> =========================================================================
> "The relationship between indexes and constraints is:
>
> o a constraint MIGHT create an index or use an existing index to
> efficient[ly] enforce itself. For example, a PRIMARY KEY constraint
> will either create an index (unique or non-unique depending) or it will
> find an existing suitable index and use it.
>
> o an index has nothing to do with a constraint. An index is an index.
>
> So, a constraint MIGHT create/use and index. An INDEX is an INDEX,
> nothing more, nothing less."
> =========================================================================
>
> Please send me the link to the document in question. Thanks.
> --
> 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 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
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.
Received on Sat Jun 23 2007 - 14:48:26 CDT
 |
 |