Today I have encountered a surprising fact:
Scenario is 10.2.0.1 Express Edition on Win32.
I have a table called job_history in the HR scheme which has a composit primary key : and there is an index for the primary key:
SQL> select index_name from user_indexes where table_name=’JOB_HISTORY’;
INDEX_NAME
Does the following info help to explain?
——————————
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX
This is my constraint in the data dictionary:
SQL> select column_name , position
2 from user_cons_columns
3 where constraint_name=’JHIST_EMP_ID_ST_DATE_PK’;
COLUMN_NAME POSITION
———————– ———————————
EMPLOYEE_ID 1
START_DATE 2
Now I drop the primary key constraint
SQL> alter table job_history drop constraint
2 JHIST_EMP_ID_ST_DATE_PK including indexes;
Table altered.
And here is the surprise:
SQL> select index_name from user_indexes where table_name=’JOB_HISTORY’;
INDEX_NAME
——————————
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX
I do not see the point here. Why is the index still there?
Evern though I have explicitly written INCLUDING INDEXES.
BUG or Feature?!
Can anyone explain?
Worked okay for me in SQL*Plus.
Test was a simple :
drop table test_a;
create table test_a (id number constraint test_a_pk primary key);
select * from user_indexes where table_name = ‘TEST_A’;
alter table test_a drop constraint test_a_pk including indexes;
select * from user_indexes where table_name = ‘TEST_A’;
Can you verify whether the constraint was dropped, but not the index, or whether neither was dropped ?
Hi Gary,
The constraint was dropped but the index not.
I have thge impression that the index is independent from the constraint here for some reason.
In your example you do not seem to have rowes in the table.
Maybe this is why it worked with you.
+:-(
Lutz,
How did you create the constraint? If you create an index first and then create a constraint, then the constraint will use the existing index instead of re-creating it from scratch.
Now, when you drop this constraint, Oracle will leave the underlying index intact and will only drop the constraint.
Here is a small example:
SQL> create table t1(a number);
Table created.
SQL> create unique index t1_pk on t1(a);
Index created.
SQL> alter table t1 add constraint t1_pk primary key (a);
Table altered.
SQL> alter table t1 drop constraint t1_pk;
Table altered.
SQL> select index_name from user_indexes where table_name = ‘T1′;
INDEX_NAME
——————————
T1_PK
SQL> drop index t1_pk;
Index dropped.
SQL> alter table t1 add constraint t1_pk primary key (a);
Table altered.
SQL> select index_name from user_indexes where table_name = ‘T1′;
INDEX_NAME
——————————
T1_PK
SQL> alter table t1 drop constraint t1_pk;
Table altered.
SQL> select index_name from user_indexes where table_name = ‘T1′;
no rows selected
SQL>
Hi Asif,
yes, I think that this is the point here.
The index seeems to be created before the constraint was added and the primary key found an usable index when it was created.
But the missleading thing here was that I had dropped the PK and still received error messages for a unique constaint which was not to be found in the Data Dictionary any more.
I know that it is the index which enforces the uniqueness and not the constraint.
It just took me quite a while to find out that the index was still there and was not automatically dropped with the constraint.
Al Hamdu Lillah I found out!
Good point!
Thanks
=;-)
BR
Lutz
This is quite strange, it used to drop the index in 9i…
SQL> create table lsc_t(x number);
Table created.
SQL> create unique index lsc_i on lsc_t(x);
Index created.
SQL> select index_name from user_indexes where table_name='LSC_T';
INDEX_NAME
------------------------------
LSC_I
SQL> alter table lsc_t add primary key (x);
Table altered.
SQL> select index_name from user_indexes where table_name='LSC_T';
INDEX_NAME
------------------------------
LSC_I
SQL> alter table lsc_t drop primary key including indexes;
Table altered.
SQL> select index_name from user_indexes where table_name='LSC_T';
no rows selected
SQL> select version from v$instance;
VERSION
-----------------
9.2.0.8.0
It was good to see you again
BTW, the syntax to drop the index is drop index
SQL> create table lsc_t(x number);
Table created.
SQL> create unique index lsc_i on lsc_t(x);
Index created.
SQL> select index_name from user_indexes where table_name='LSC_T';
INDEX_NAME
------------------------------
LSC_I
SQL> alter table lsc_t add primary key (x);
Table altered.
SQL> select index_name from user_indexes where table_name='LSC_T';
INDEX_NAME
------------------------------
LSC_I
SQL> alter table lsc_t drop primary key drop index;
Table altered.
SQL> select index_name from user_indexes where table_name='LSC_T';
no rows selected
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.4.0
the INCLUDING INDEXES clause is not related to DROP PRIMARY KEY but to the EXCHANGE PARTITION clause
Specify INCLUDING INDEXES if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table).
Hi Laurent,
I think that the crux of the bisquit here is that Oracle will drop the index if it was created automatically with a new Primary Key constraint.
If you add a constraint after the index was created it seems to be not linked with the index and a DROP CONSTRAINT will leave the index as it was.
=;-)
Lutz, as you see in my demo, this was not the case in 9i (where the index was dropped even if it was created before the constraint). But to have full control, use :
alter table drop primary key keep index;
or
alter table drop primary key drop index;
Cool Laurent, this is it!
Just like to add on to this
this behavior will vary across Oracle 10.2.0.1 (this is a functionality in this release as per metalink note) , 10.2.0.2,10.2.0.3. Also you will find this working in 32 bit environment for 10.2.0.1 but not in 64-bit with 10.2.0.1
Unique Index is physical attribute based on unique values in table.
Primary key is constraint (not null and unique index together).
So conclusion: Why would dropping constraints apply and physical delete in database!!?
Simple if you think in this way…
P.S.
Almost similar (but far from quite the same) FK and index on foreign key column.