Thursday, December 27, 2012

Dropping Primary Key and keep index

Here we are going to see how we can drop primary key and keep index meaning without touch index 
  •  If  add primary key on column the automatically create unique index 
 If  drop the primary key from column then drop the primary key as well as index . if u keep the index with drop primary key then 

SQL>create table test1
(id number constraints test1_id_pk primary key,name varchar2(20));

SQL> select constraint_name,constraint_type  from  user_constraints where table_
name='TEST1';

CONSTRAINT_NAME                C
------------------------------ -
TEST1_ID_PK                    P

SQL> select index_name  from  user_indexes where table_name='TEST1';

INDEX_NAME
------------------------------
TEST1_ID_PK




 SQL> alter table test1 drop primary key keep index;

Table altered.

 SQL> select constraint_name,constraint_type  from  user_constraints where table_
name='TEST1';

no rows selected

SQL> select index_name  from  user_indexes where table_name='TEST1';

INDEX_NAME
------------------------------
TEST1_ID_PK