How to Cascade Enable primary keys

[anirudhadeo]

Hi,
I am looking for some good options for a problem i am having.
My problem will be solved if I get something to disable primary key of a table and then again enable it.
but the primary key I want to disable is reffered by another tables as foriegn keys.
I can disable the primary using
'alter table Tname disable primary key cascade'

but I donno how to enable it cascade(that is enable all the constraints that were disable becoz of above statement) Is there any way. PLease suggest.

Thanx

 

[did02]

U can try this. Hope this help you.
Did

accept owner prompt "Enter Table Owner [Enter For All]:"
accept table_name prompt "Enter Table Name [Enter For All]:"

set pagesize 0
set line 150
set heading off
set feedback off
set echo off


spool c:\temp\script_disable.sql

select 'spool c:\temp\script_disable.log' from dual;

-- to disable FK referencing the PK of the given table
select 'alter table '||b.OWNER||'.'||b.TABLE_NAME||' disable constraint '||b.CONSTRAINT_NAME||';'
from all_constraints a, all_constraints b
where a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME
and a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and b.CONSTRAINT_TYPE='R';

-- to disable the PK of the given table
select 'alter table '||a.OWNER||'.'||a.TABLE_NAME||' disable constraint '||a.CONSTRAINT_NAME||';'
from all_constraints a
where a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and a.CONSTRAINT_TYPE in ('P','U');

select 'spool off' from dual;

spool off


spool c:\temp\script_enable.sql

select 'spool c:\temp\script_enable.log' from dual;

-- to enable the PK of the given table
select 'alter table '||a.OWNER||'.'||a.TABLE_NAME||' enable constraint '||a.CONSTRAINT_NAME||';'
from all_constraints a
where a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and a.CONSTRAINT_TYPE in ('P','U');

-- to enable FK referencing the PK of the given table
select 'alter table '||b.OWNER||'.'||b.TABLE_NAME||' enable constraint '||b.CONSTRAINT_NAME||';'
from all_constraints a, all_constraints b
where a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME
and a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and b.CONSTRAINT_TYPE='R';

select 'spool off' from dual;

spool off

pause

set pagesize 15
set echo on
set heading on
set feedback on

@c:\temp\script_disable.sql

prompt 'do what you have to do !!!'
pause

@c:\temp\script_enable.sql