RE: Question about Append hint in Insert
- From: Rachel Carmichael <wisernet100@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 21 Jun 2004 12:59:02 -0700 (PDT)
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)
1) same code, except drop the constraint not disable
2) create a unique index before you create the constraint, then drop
constraint (should still drop the index)
3) 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@xxxxxxxxxxx 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
