RE: Question about Append hint in Insert

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Mon, 21 Jun 2004 12:59:02 -0700 (PDT)
Message-ID: <20040621195902.55371.qmail@web60710.mail.yahoo.com>

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)

same code, except drop the constraint not disable
create a unique index before you create the constraint, then drop constraint (should still drop the index)
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
--- Jared.Still_at_radisys.com wrote:
> > you can disable the constraint without the need to drop the index;
> You may also do that with an index created as part of the primary key
> constraint:
>
>
> 11:47:28 SQL>drop table x;
>
> Table dropped.
>
> 11:47:28 SQL>
> 11:47:28 SQL>create table x
> 11:47:28 2 as
> 11:47:28 3 select distinct object_name
> 11:47:28 4 from user_objects
> 11:47:28 5 /
>
> Table created.
>
> 11:47:28 SQL>
> 11:47:28 SQL>alter table x add constraint x_pk
> 11:47:28 2 primary key(object_name)
> 11:47:28 3 /
>
> Table altered.
>
> 11:47:28 SQL>
> 11:47:28 SQL>select index_name , uniqueness, status
> 11:47:28 2 from user_indexes
> 11:47:28 3 where table_name = 'X'
> 11:47:28 4 /
>
> INDEX NAME UNIQUENES STATUS
> ------------------------------ --------- --------
> X_PK UNIQUE VALID
>
> 1 row selected.
>
> 11:47:28 SQL>
> 11:47:28 SQL>select constraint_name, constraint_type, status
> 11:47:28 2 from user_constraints
> 11:47:28 3 where table_name = 'X'
> 11:47:28 4 /
>
> CONSTRAINT_NAME C STATUS
> ------------------------------ - --------
> X_PK P ENABLED
>
> 1 row selected.
>
> 11:47:28 SQL>
> 11:47:28 SQL>alter table x disable primary key keep index;
>
> Table altered.
>
> 11:47:28 SQL>
> 11:47:28 SQL>select index_name , uniqueness, status
> 11:47:28 2 from user_indexes
> 11:47:28 3 where table_name = 'X'
> 11:47:28 4 /
>
> INDEX NAME UNIQUENES STATUS
> ------------------------------ --------- --------
> X_PK UNIQUE VALID
>
> 1 row selected.
>
> 11:47:28 SQL>
> 11:47:28 SQL>select constraint_name, constraint_type, status
> 11:47:28 2 from user_constraints
> 11:47:28 3 where table_name = 'X'
> 11:47:28 4 /
>
> CONSTRAINT_NAME C STATUS
> ------------------------------ - --------
> X_PK P DISABLED
>
> 1 row selected.
>
> 11:47:28 SQL>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 21 2004 - 14:55:51 CDT