9i lets you do this as long as you use the keep index syntax... For 8i your statement is correct... Modifying Jared's example... SQL> drop table x; Table dropped. SQL> SQL> create table x 2 as 3 select distinct object_name 4 from user_objects 5 / Table created. SQL> SQL> create unique index x_idx on x(object_name) 2 / Index created. SQL> SQL> alter table x add constraint x_pk 2 primary key(object_name) 3 using index x_idx 4 / Table altered. SQL> SQL> select index_name , uniqueness, status 2 from user_indexes 3 where table_name = 'X' 4 / INDEX_NAME UNIQUENES STATUS ------------------------------ --------- -------- X_IDX UNIQUE VALID SQL> SQL> select constraint_name, constraint_type, status 2 from user_constraints 3 where table_name = 'X' 4 / CONSTRAINT_NAME C STATUS ------------------------------ - -------- X_PK P ENABLED SQL> SQL> alter table x drop primary key keep index; Table altered. SQL> SQL> select index_name , uniqueness, status 2 from user_indexes 3 where table_name = 'X' 4 / INDEX_NAME UNIQUENES STATUS ------------------------------ --------- -------- X_IDX UNIQUE VALID SQL> SQL> select constraint_name, constraint_type, status 2 from user_constraints 3 where table_name = 'X' 4 / no rows selected SQL> Rachel Carmichael wrote: >you may DROP the constraint without losing the index.... > >if you drop a pk constraint, even if the unique index has been created >separately, the index goes away too (this is from memory but I seem to >recall spending a lot of time testing this ... and cursing) > >if the index is non-unique, dropping the constraint will not drop the >index. > >can you try your test case several different ways (I'm swamped, don't >have time to run this) > >1) same code, except drop the constraint not disable >2) create a unique index before you create the constraint, then drop >constraint (should still drop the index) >3) create a non-unique index before you create the constraint, then >drop constraint (should keep the index) > >and please, if I'm wrong, let the list know, not just me :) > >Rachel > > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------