Enabling Foreign Keys
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Enabling Foreign Keys

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    hi there,

    I was told to enable foreign keys. How do I get a list of them (with status) and enable them?

    Thanks,
    Nirasha

  2. #2
    Join Date
    Aug 2000
    Posts
    132
    The data dictionary view you want to look at is all_constraints,
    I use the following query to spool out a text file that will enable all of my foreign key restraints that have been disabled

    select 'alter table SCHEMANAME.' ||table_name|| ' enable constraint '||constraint_name||';'
    from all_constraints where owner = 'SCHEMANAME'
    and constraint_name NOT LIKE 'SYS%'

    Good luck!

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    <font face=courier>
    select owner,constraint_name,table_name,r_owner, r_constraint_name,status
    from dba_constraints
    where constraint_type='R' and owner<>'SYSTEM'
    /
    </font>

    [Edited by sreddy on 01-17-2001 at 01:05 PM]

  4. #4
    Join Date
    Nov 2000
    Posts
    205
    thanks guys,

    I used a combination of both of these to create.

    select 'alter table FNDRYR0200.' ||table_name|| ' enable constraint '|| constraint_name||';'
    from dba_constraints
    where constraint_type='R' and owner = 'FNDRYR0200';

    It worked real well.

    thanks again,
    nirasha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width