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;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.