Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: constraints and indexes
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.
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>
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
>=20
>=20
>=20
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 09 2005 - 16:49:35 CDT