Re: orace sql query

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1182871437.485877_at_bubbleator.drizzle.com...
>
> You can not have a primary key or unique constraint without an index.
> In that you are correct: Oracle won't allow it.
>
> The point I was making was merely a clarification. The index is used
> to speed the search for values not to enforce the constraint. The
> description in the manuals/docs is misleading.

It is amazing how tedious and convoluted you have to get to cover all the options. It's not surprising that the Oracle manuals never keep up. For example:

SQL> alter table t1 add constraint t1_pk primary key(object_id) disable validate;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T1';

no rows selected

SQL> insert into t1 select * from t1 where rownum = 1; insert into t1 select * from t1 where rownum = 1 *
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (TEST_USER.T1_PK) disabled and validated

Given the combinations of rely, enable, and validate, it's quite surprising how many things you have to test to explain all the options.

In principle the OP (from all those posts ago) could have a data set with guaranteeably correct data, with a declared primary key, without a supporting index - provided he (or she) never wanted to change the data. (Wasn't there a question about read-only tables a few months ago ?)

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html