Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Regarding Oracle Constraints and Indexes

Re: Regarding Oracle Constraints and Indexes

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Thu, 03 Feb 2005 02:58:07 GMT
Message-ID: <3rgMd.22063$MX2.9806@twister.nyroc.rr.com>

"rbhatraju" <venkataramana.bhatraju_at_nospam.com> wrote in message news:9c7470563109f6c09c55bb24a18d77b0_at_localhost.talkaboutdatabases.com...
> Actually I have created a new table with a not null column cust_id,
> followed by a unique index and then added a primary key on this column. It
> gave me a system generated name for the PK constraint. So to have a
> legitimate name, I dropped the primary key using
> SQL> ALTER table tablename drop primary key;"
>
> SQL> select index_name, tablespace_name from dba_indexes where owner='XXX'
> and table_name='XXX';
>
> no rows selected
>
> When I searched for index it gave me no rows.
>
> My question is dropping a primary key drops the index as well in oracle?
> This won't behave the same way in DB2 (even if the primary key gets
> dropped the unique index remains)
>
> Thanks and regards in advance.
> Venkat
>

Well oracle is not DB2.

Read the following:

ORA92> create table mytable (a number, b number, c number);

Table created.

ORA92> alter table mytable add constraint mytable_pk primary key (a);

Table altered.

ORA92> select index_name from user_indexes where table_name = 'MYTABLE';

INDEX_NAME



MYTABLE_PK ORA92> alter table mytable drop constraint mytable_pk;

Table altered.

ORA92> select index_name from user_indexes where table_name = 'MYTABLE';

no rows selected

ORA92> alter table mytable add constraint mytable_pk primary key (a);

Table altered.

ORA92> select index_name from user_indexes where table_name = 'MYTABLE';

INDEX_NAME



MYTABLE_PK ORA92> alter table mytable drop constraint mytable_pk KEEP INDEX;

Table altered.

ORA92> select index_name from user_indexes where table_name = 'MYTABLE';

INDEX_NAME



MYTABLE_PK Anurag Received on Wed Feb 02 2005 - 20:58:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US