Re: orace sql query

fitzjarrell@xxxxxxx wrote:
On Jun 22, 3:45 pm, DA Morgan <damor...@xxxxxxxxx> wrote:
EdStevens wrote:
On Jun 22, 12:15 pm, sathishkes...@xxxxxxxxx wrote:
i want to create table with primary key but with out index
How do you think Oracle enforces the PK?
Not with an index. Indexes have nothing to do with primary key
enforcement. Consider, for example, a deferred constraint which
is built with a non-unique index.
--
Daniel A. Morgan
University of Washington
damor...@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

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:3051352977204
=========================================================================
"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
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

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 ... Puget Sound Oracle Users Groupwww.psoug.org ...
    (comp.databases.oracle.server)
  • 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: Difference between creating a Unique Constraint and a Unique Index
    ... background when you specify a PK or Unique constraint. ... So use PK and Uniques to enforce design requirements...Indexes are ... the indexes created to enforce data integrity. ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • RE: Difference between creating a Unique Constraint and a Unique Index
    ... Second, you made mention of PK and GUID, which I am assuming you implied ... EmployeeID and no Unique Constraint is ever defined on the Candidate Key ... GUID PK is also defined as the Clustered Index, if there is one at all. ... > a unique index will not enforce uniqueness. ...
    (microsoft.public.sqlserver.server)
  • Re: orace sql query
    ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ... Create a generic table without a primary key: ...
    (comp.databases.oracle.server)