Re: disable pk works differently in oracle 9 and oracle 10?

According to the Administrator's Guild 10g Release 2 http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/general.htm#i1006732

 Modifying, Renaming, or Dropping Existing Integrity Constraints

You can use the ALTER TABLE statement to enable, disable, modify, or drop a constraint. When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise.

On 6/8/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com> wrote:
>
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> genegurevich_at_discoverfinancial.com
> 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