Oracle: disable all constraints referencing the table

Posted on by


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;

2 thoughts on “Oracle: disable all constraints referencing the table”

  2. The most simple way is to use “ENABLE” command:

    EXECUTE IMMEDIATE
    'ALTER TABLE '||cur.owner||'.'||cur.table_name||
    ' MODIFY CONSTRAINT '||cur.constraint_name||' ENABLE';

    However, it’s even better to build the list of the constraints BEFORE:

    select 'ALTER TABLE '||cur.owner||'.'||cur.table_name|| ' MODIFY CONSTRAINT '||cur.constraint_name||' ENABLE;'
    from
    (
    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'
    ) cur;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.