Oracle: disable all constraints referencing the table

BEGIN
FOR cur IN (SELECT fk.owner, fk.constraint_name , fk.table_name
    FROM all_constraints fk, all_constraints pk
    WHERE fk.CONSTRAINT_TYPE = 'R' AND
    pk.owner = '&which_owner' AND
    fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
           AND pk.TABLE_NAME = '&which_table'
        ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||cur.owner||'.'||cur.table_name||
                ' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE';
   END LOOP;
END;

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>