RE: ENABLING FOREIGN KEY CONSTRAINTS

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 26 Sep 2001 11:08:57 -0700
Message-ID: <F001.00399AD0.20010926110530@fatcity.com>

> -----Original Message-----
> From: Harvinder Singh [mailto:Harvinder.Singh_at_MetraTech.com]
>
> When we do alter table table_name disable primary key
> cascade;......it also
> disable all the foreign key constraints....
> but when we after do alter table table_name enable primary
> key......it does
> not enable foreign keys........
> is there any syntax that we can enable all th foreign keys referencing
> particular table......

Use the "SQL from SQL" approach:

select

   'alter table "' || b.owner || '"."' || b.table_name ||
   '" enable constraint "' || b.constraint_name || '" ;'
      as sql_text

from
   dba_constraints a, dba_constraints b
where

   a.owner = 'TABLE_WITH_PK_OWNER'
   and a.table_name = 'TABLE_WITH_PK_NAME'
   and a.constraint_type = 'P'
   and b.constraint_type = 'R'
   and b.r_owner = a.owner
   and b.r_constraint_name = a.constraint_name
   and b.status = 'DISABLED' ;

The query will generate all the SQL statements you need to re-enable the foreign key constraints. Received on Wed Sep 26 2001 - 13:08:57 CDT