Re: Row in USER_INDEXES table not deleted when constraint is dropped

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 07 Oct 2006 10:21:28 -0700
Message-ID: <1160241686.871705@bubbleator.drizzle.com>

HansF wrote:
> On Fri, 06 Oct 2006 21:50:19 -0700, Luso Joe wrote:
>

>> My guess is there is a link between DBA_DEPENDCIES, DBA_INDEXES and
>> DBA_CONSTRAINTS, but I won't be able to check till next week. I'll
>> followup if I find something substantive, unless someone beats me to
>> it.

>
> SQL> select distinct delete_rule, constraint_type from dba_constraints;
>
> DELETE_RU C
> --------- -
> V
> U
> P
> ?
> C
> CASCADE R
> NO ACTION R
> O
>
> 8 rows selected.
>
> SQL>
That is not what I interpreted the OP to be asking. Here's the behavior I think is being referenced.

CREATE TABLE t (
idcol NUMBER(5),
chcol VARCHAR2(5));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(idcol)
USING INDEX; SELECT table_name, index_name
FROM user_indexes;

ALTER TABLE t DROP PRIMARY KEY;

SELECT table_name, index_name
FROM user_indexes;

CREATE UNIQUE INDEX ix_t
ON t(idcol);

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(idcol)
USING INDEX; SELECT table_name, index_name
FROM user_indexes;

ALTER TABLE t DROP PRIMARY KEY;|

SELECT table_name, index_name
FROM user_indexes;

Note that in the second case, where the index is pre-existing, dropping the PK does not also drop the index. I can not find anywhere in the data dictionary where this is recorded though it must be there.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Oct 07 2006 - 12:21:28 CDT