Modify the Primary Key Order

I have created a composite primary key (a,b,c) in the table. It has references in other table also.

Now I want to modify the primary key order - (b,c,a). How to go about it?

You can try this sequence:

1) disable primary key related foreign constraint on child table;

2) disable primary key constaint on present table

3) drop index

4) recreate index

5) enable all disabled constraint

The enclosed scrip will do it:

CODE

FILE: recreate_pk_ddl.sql

set pagesize 999 heading off verify off termout off

set feedback off sqlcase upper newpage 3 rowsize 400

SELECT constraint_name column1,5 column2, 0 column3,
'alter table '||table_name||' add constraint '||table_name||'_pk primary key ('
FROM user_constraints where
constraint_type = 'P'

UNION

SELECT constraint_name,10,position,
decode(position,1,' ',',')||column_name
from user_cons_columns
where constraint_name in (select constraint_name from user_constraints
where constraint_type = 'P')

UNION

SELECT constraint_name, 20,99,' )' from user_constraints

union

select constraint_name, 30,99,' using index' from user_constraints

union

select constraint_name, 40,99,' pctfree 10' from user_constraints

union

select constraint_name, 50,99,' tablespace indexes ' from user_constraints

union

select constraint_name, 60,99,' storage( ' from user_constraints

union

select constraint_name, 70,99,' initial ' from user_constraints

union

select constraint_name, 80,99,' next ' from user_constraints

union

select constraint_name, 90,99,' pctincrease 0 );' from user_constraints

union

select constraint_name, 100,99, ' ' from user_constraints

order by 1,2,3;