Re: A Primary Key and Foreign key

From: Robert Liziniewicz <robertl_at_ci.comarch.pl>
Date: Tue, 01 Dec 1998 17:19:12 +0100
Message-ID: <366416FF.1E89B9E0@ci.comarch.pl>

You can disable all foreign key constraints on a schema using script like the following:

set pages 0
set feedback off

spool x.sql
select 'alter table '||table_name||' disable constraint '||constraint_name ||';'
from user_constraints where status = 'ENABLED' and constraint_type = 'R'; spool off
set feedback on
@@x
-- just check if all constraints were disabled
select status,count(*) from user_constraints

group by status;

of course there's another script to turn the constraints on again

set pages 0
set feedback off

spool x.sql
select 'alter table '||table_name||' enable constraint '||constraint_name ||';'
from user_constraints where status = 'DISABLED' and constraint_type = 'R'; spool off
set feedback on
@@x
-- just check if all constraints were enabled
select status,count(*) from user_constraints

group by status;

I suggest not to disable PK, nor CHECK constraints - You'll have better view on the load process and avoid duplicates or inapropriate values.

Remember about disabling triggers also!!!!

Best Regards,
Robert

Robert Liziniewicz                    C.I. HTS-ComArch
Robert.Liziniewicz_at_ci.comarch.pl      ul. Ujastek 10, Krakow
                                      tel. (+48 12) 644-66-31
                                           (+48 601) 47-86-81

Received on Tue Dec 01 1998 - 10:19:12 CST