disable pk works differently in oracle 9 and oracle 10? 2006-06-08 - By Eric Jenkinson
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@(protected)> 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@(protected)
> [mailto:oracle-l-bounce@(protected)] On Behalf Of
> 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 (See ORA-00942.ora-code.com): 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
>
>
>
<div>According to the Administrator's Guild 10g Release 2</div>
<div><a href="http://download-east.oracle.com/docs/cd/B19306_01/server.102
/b14231/general.htm#i1006732">http://download-east.oracle.com/docs/cd/B19306_01
/server.102/b14231/general.htm#i1006732</a></div>
<div> </div>
<div>
<h3 class="sect2">Modifying, Renaming, or Dropping Existing Integrity
Constraints</h3>
<p>You can use the <code>ALTER TABLE</code> statement to enable, disable,
modify, or drop a constraint. When the database is using a <code>UNIQUE</code>
or <code>PRIMARY KEY</code> index to enforce a constraint, and constraints
associated with that index are dropped or disabled
<a id="sthref1922" name="sthref1922"></a><a id="sthref1923" name="sthref1923"><
/a><a id="sthref1924" name="sthref1924"></a><a id="sthref1925" name="sthref1925"
></a>, the index is dropped, unless you specify otherwise.</p>
</div>
<div> </div>
<div> </div>
<div><span class="gmail_quote">On 6/8/06, <b class="gmail_sendername">Bobak,
Mark</b> <<a href="mailto:Mark.Bobak@(protected)">Mark.Bobak@(protected)
.com</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">Interesting.....<br><br>I never noticed that
in 10g, but I do agree that what you observed in<br>9i is what I
would consider expected behavior.
<br><br>As I recall, (at least for 9i), if you disable a primary key
and the<br>underlying index is unique, it is dropped. If you disable
a primary key<br>and the underlying index is non-unique, it will remain.<br><br
>Logically, I'm not sure if I agree w/ the 10g behavior. It would be
<br>possible to disable the PK constraint, and still be restricted from<br
>entering duplicate records, due to the existance of the unique index.
I<br>tested with unique constraint as well, and it behaves the same as PK
(in
<br>10g).<br><br>So, is it a bug or a feature? ;-)<br><br>-Mark<br><br><br>--
<br>Mark J. Bobak<br>Senior Oracle Architect<br>ProQuest Information &
Learning<br><br>For a successful technology, reality must take precedence over
public
<br>relations, for Nature cannot be fooled. --Richard P. Feynman,
1918-1988<br><br><br>-- --Original Message-- --<br>From: <a href="mailto:oracle
-l-bounce@(protected)">oracle-l-bounce@(protected)</a><br>[mailto:<a href=
"mailto:oracle-l-bounce@(protected)">
oracle-l-bounce@(protected)</a>] On Behalf Of<br><a href="mailto:genegurevich
@(protected)">genegurevich@(protected)</a><br>Sent: Thursday
, June 08, 2006 1:40 PM<br>To: oracle-l<br>Subject: disable pk works differently
in oracle 9 and oracle 10?
<br><br>Hi all:<br><br>I have noticed that something that I was able to do in
oracle9 can't be<br>done in oracle10. This is very annoying and I would
appreciate any<br>thoughts on this:<br><br>Oracle 9:<br><br>SQL> select *
from v$version;
<br>Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL<br
>Release 9.2.0.4.0 - Production<br>CORE 9.2.0.3.0
Production<br>TNS for IBM/AIX RISC System/6000:
Version 9.2.0.4.0 - Production NLSRTL
<br>Version 9.2.0.4.0 - Production<br><br>SQL> drop table test1;<br><br
>Table dropped.<br><br>SQL> create table test1 (f1 number);<br><br>Table
created.<br><br>SQL> create unique index test1_pk on test1 (f1);<br>
<br>Index created.<br><br>SQL> alter table test1 add constraint test1_pk
primary key (f1) using<br>index;<br><br>Table altered.<br><br>SQL>
; select index_name from dba_indexes where table_name = 'TEST1';<br>TEST1_PK
<br><br>SQL> alter table test1 disable primary key;<br><br>Table altered.<br
><br>SQL> select index_name from dba_indexes where table_name = 'TEST1';<br>
<br>no rows selected<br><br>As you see when I disable the primary key, my index
goes away as well.
<br>When I do the same in oracle 10G however things are different:<br><br
>Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi PL/SQL<br
>Release 10.2.0.2.0 - Production<br>CORE 10.2.0.2.0
Production<br>
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio NLSRTL<br
>Version 10.2.0.2.0 - Production<br><br>5 rows selected.<br><br>SQL> drop
table test1;<br>drop table test1<br>
*<br>ERROR at line 1:<br>
ORA-00942 (See ORA-00942.ora-code.com): table or view does not exist<br><br><br>SQL> create
table test1 (f1 number);<br><br>Table created.<br><br>SQL> create
unique index test1_pk on test1 (f1);<br><br>Index created.<br><br>SQL> alter
table test1 add constraint test1_pk primary key (f1) using
<br>index;<br><br>Table altered.<br><br>SQL> select index_name from dba
_indexes where table_name = 'TEST1';<br>TEST1_PK<br><br>SQL> alter
table test1 disable primary key;<br><br>Table altered.<br><br>SQL>
select index_name from dba_indexes where table_name = 'TEST1';
<br>TEST1_PK<br><br>Here the index stays after the PK is disabled.<br><br>This
is a big difference IMO and I wonder whether this is a new feature<br>in
oracle10 or whether this is something I am not doing correctly. If<br>
anyone has any insight on that please let me know<br><br>thank you<br><br>Gene
Gurevich<br>Oracle Engineering<br>224-405-4079<br><br><br>--<br><a href="http:/
/www.freelists.org/webpage/oracle-l">http://www.freelists.org/webpage/oracle-l
</a><br><br><br>--<br><a href="http://www.freelists.org/webpage/oracle-l">http:
//www.freelists.org/webpage/oracle-l</a><br><br><br></blockquote></div><br>
|
|
