Interesting..... I never noticed that in 10g, but I do agree that what you observed in 9i is what I would consider expected behavior. As I recall, (at least for 9i), if you disable a primary key and the underlying index is unique, it is dropped. If you disable a primary key and the underlying index is non-unique, it will remain. Logically, I'm not sure if I agree w/ the 10g behavior. It would be possible to disable the PK constraint, and still be restricted from entering duplicate records, due to the existance of the unique index. I tested with unique constraint as well, and it behaves the same as PK (in 10g). So, is it a bug or a feature? ;-) -Mark -- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of genegurevich@xxxxxxxxxxxxxxxxxxxxx Sent: Thursday, June 08, 2006 1:40 PM To: oracle-l Subject: disable pk works differently in oracle 9 and oracle 10? Hi all: I have noticed that something that I was able to do in oracle9 can't be done in oracle10. This is very annoying and I would appreciate any thoughts on this: Oracle 9: SQL> select * from v$version; Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production SQL> drop table test1; Table dropped. SQL> create table test1 (f1 number); Table created. SQL> create unique index test1_pk on test1 (f1); Index created. SQL> alter table test1 add constraint test1_pk primary key (f1) using index; Table altered. SQL> select index_name from dba_indexes where table_name = 'TEST1'; TEST1_PK SQL> alter table test1 disable primary key; Table altered. SQL> select index_name from dba_indexes where table_name = 'TEST1'; no rows selected As you see when I disable the primary key, my index goes away as well. When I do the same in oracle 10G however things are different: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi PL/SQL Release 10.2.0.2.0 - Production CORE 10.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio NLSRTL Version 10.2.0.2.0 - Production 5 rows selected. SQL> drop table test1; drop table test1 * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table test1 (f1 number); Table created. SQL> create unique index test1_pk on test1 (f1); Index created. SQL> alter table test1 add constraint test1_pk primary key (f1) using index; Table altered. SQL> select index_name from dba_indexes where table_name = 'TEST1'; TEST1_PK SQL> alter table test1 disable primary key; Table altered. SQL> select index_name from dba_indexes where table_name = 'TEST1'; TEST1_PK Here the index stays after the PK is disabled. This is a big difference IMO and I wonder whether this is a new feature in oracle10 or whether this is something I am not doing correctly. If anyone has any insight on that please let me know thank you Gene Gurevich Oracle Engineering 224-405-4079 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l