I was wondering about that as well. I finally got my hands on metalink
note #139666.1 (2003, 9i only). I am thinking 10g handles the
association a little differently. Gene's case acts like Case 2 from the
note, but I am not exactly sure why.
Interesting to note, as well, that the behavior changes a bit depending
on how you define the PK in the first place. For all-around cleanliness
and portability, it seems "nicer" to declare the constraint (and index)
along with the table definition.
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Thursday, June 08, 2006 1:06 PM
To: genegurevich@(protected)
Subject: RE: disable pk works differently in oracle 9 and oracle 10?
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@(protected)
[mailto:oracle-l-bounce@(protected)
genegurevich@(protected)
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
