DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: disable constraints

  1. #1
    Join Date
    Nov 2000
    Posts
    198
    I am trying to TRUNCATE ALL tables in my schema
    and I keep getting
    "unique/primary keys in table referenced by enabled foreign keys".

    should I disable all constraints ?? if so how can I do it
    in one statment???

    thanks,

  2. #2
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    create a dynamic script that will find all of the constraints and generate the sql to disable them, then you will have to do the same thing to enable them. Here is what I use:

    set termout off
    clear columns
    column sqlline newline

    set feed off
    set pages 0

    spool E:\DIR\disable_cons.sql

    select 'spool E:\dir\disable_cons.log' from dual
    /

    select ' ' sqlline
    ,'prompt ' sqlline
    ,'prompt Disabling Constraint: '||constraint_name||' from Table '||table_name||'...' sqlline
    ,'prompt ' sqlline
    ,'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' sqlline
    from sys.dba_constraints
    where ( constraint_type = 'R' or
    (constraint_type = 'C' and constraint_name not like 'SYS%'))
    and owner NOT IN ('SYS','SYSTEM')
    and status = 'ENABLED'
    /
    select 'spool off' from dual
    /
    spoo off

    set feed on
    set pages 60
    set termout on
    prompt Output is in... E:\dir\disable_cons.sql


    You can just edit this to fit your db and also create a script to go and enable all of the constraints as well. I just write a script to run this file then run the file it creates, I suppose you could just use a "run disable_cons.sql" line at the end of this one to do the same thing.

    You may want to do the same for the triggers.

    HTH

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378
    set feed off pages 0 head off echo off line 100 ver off
    col col1 newline

    REM #
    REM # create script to disable all referencial constraints
    REM #
    spool /tmp/disable.sql
    select 'alter table '|| r_owner ||'.'||
    table_name ||' disable constraint '||
    constraint_name ||';'
    from dba_constraints
    where r_owner = upper('')
    and constraint_type = 'R';
    spool off
    REM #
    REM # create script to enable constraints
    REM #
    spool /tmp/enable.sql
    select 'alter table '|| r_owner ||'.'||
    table_name ||' enable constraint '||
    constraint_name ||';'
    from dba_constraints
    where r_owner = upper('')
    and constraint_type = 'R';
    spool off


    - magnus
    change to your own schema.
    to run just use: @/tmp/disable.sql
    on WIN32 change directory to something like: d:\disable.sql

    then enable them by running the other script "enable.sql"

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