Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Regarding Oracle Constraints and Indexes
"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
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
Table altered.
ORA92> select index_name from user_indexes where table_name = 'MYTABLE';
INDEX_NAME