Re: constraints and indexes

From: Vlad Sadilovskiy <vlovsky_at_gmail.com>
Date: Thu, 9 Jun 2005 16:44:12 -0400
Message-ID: <df9f25d505060913444bf1ba10@mail.gmail.com>

If "using index" clause is specified when creating primary key then subsequent "drop constraint" will not drop your index unless explicitly asked by "drop index" clause.

Alternatively, if the index generated by the system, to keep it you should specify "keep index" clause or "drop constraint" will drop the index along.

PK on manulaly created index and dropped without additional clauses in the command. SQL> create table a(a number primary key using index (create index a_i on a= (a))) ;

Table created.

SQL> select constraint_name, constraint_type, validated, index_name 2 from user_constraints where table_name=3D'A';

CONSTRAINT_NAME                C VALIDATED     INDEX_NAME
------------------------------ - ------------- ----------------------------=
--
SYS_C002654                    P VALIDATED     A_I

SQL> alter table a drop constraint SYS_C002654;

Table altered.

SQL> select index_name, table_name from user_indexes where table_name=3D'A'= ;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
A_I                            A <-- index still here

SQL> drop table a;

Table dropped.

2. PK is using system generated index and dropped without additional clauses in the command.

SQL> create table a(a number primary key);

Table created.

SQL> select constraint_name, constraint_type, validated, index_name 2 from user_constraints where table_name=3D'A';

CONSTRAINT_NAME                C VALIDATED     INDEX_NAME
------------------------------ - ------------- ----------------------------=
--
SYS_C002655                    P VALIDATED     SYS_C002655

SQL> alter table a drop constraint SYS_C002655;

Table altered.

SQL> select index_name, table_name from user_indexes where table_name=3D'A'= ;

no rows selected <-- it was automatically dropped

SQL> drop table a;

Table dropped.

3. PK is using manually created index and dropped with "drop index" clause. SQL> create table a(a number primary key using index (create index a_i on a= (a)))
;

Table created.

SQL> select constraint_name, constraint_type, validated, index_name 2 from user_constraints where index_name=3D'A_I';

CONSTRAINT_NAME                C VALIDATED     INDEX_NAME
------------------------------ - ------------- ----------------------------=
--
SYS_C002656                    P VALIDATED     A_I

SQL> alter table a drop constraint SYS_C002656 drop index;

Table altered.

SQL> select constraint_name, constraint_type, validated, index_name 2 from user_constraints where table_name=3D'A';

no rows selected <-- no index, we asked for it

SQL> drop table a;

Table dropped.

4. PK is using system generated index and dropped with "keep index" clause. SQL> create table a(a number primary key);

Table created.

SQL> select constraint_name, constraint_type, validated, index_name 2 from user_constraints where table_name=3D'A';

CONSTRAINT_NAME                C VALIDATED     INDEX_NAME
------------------------------ - ------------- ----------------------------=
--
SYS_C002657                    P VALIDATED     SYS_C002657

SQL> alter table a drop constraint SYS_C002657 keep index;

Table altered.

SQL> select index_name, table_name from user_indexes where table_name=3D'A'= ;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_C002657                    A <-- here we go index is still here

SQL>

Vlad

On 6/9/05, Chris Stephens <cstephens16_at_gmail.com> wrote:
> I've discovered an odd different between our testing and staging
> environments here that I am unable to explain/duplicate. It probalby
> exemplifies my lack of understanding of indexes and constraints.

>=20

> We have the exact same table in both environments with the exact same
> primary key constraint and supporting index. In one database when i
> disable the constraint, the corresponding index is dropped. In the
> other when i disable the constraint, the corresponding index remains.=3D2=
0
> Any idea what the difference is?

>=20

> I thought it might have to do with creating the index first as opposed
> to just creating the constraint and implicitly creating the index but
> that doesn't seem to be the case.

>=20

> I'm off to read the series of articles Jonathan Lewis wrote for
> dbazine on constraints but thought i'd post this first. (probably the
> wrong order but whatever).

>=20

> Thanks for any insight.
> Chris
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 09 2005 - 16:49:35 CDT