Programmers Blog

I found this very helpful procedure http://www.orafaq.com/node/2024. But it does not work when copy-pased. Here you can download the compile-ready for Oracle 10g version of this procedure. disable_constraints.sql

CREATE OR REPLACE PROCEDURE MANAGE_USER_FK_PK_UK(
    OPERATION VARCHAR2 ,
    FK        BOOLEAN DEFAULT TRUE,
    PK        BOOLEAN DEFAULT TRUE,
    UK        BOOLEAN DEFAULT TRUE)
IS
  ST VARCHAR2(255);
  CURSOR R
  IS
    SELECT TABLE_NAME,
      CONSTRAINT_NAME
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'R';
  CURSOR P
  IS
    SELECT TABLE_NAME,
      CONSTRAINT_NAME
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'P';
  CURSOR U
  IS
    SELECT TABLE_NAME,
      CONSTRAINT_NAME
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'U';
  
  E R%ROWTYPE;
  
BEGIN
  IF UPPER(OPERATION) IN ('DROP','DISABLE') THEN
    IF FK THEN
      BEGIN
        FOR E IN R
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF PK THEN
      BEGIN
        FOR E IN R
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
      BEGIN
        FOR E IN P
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF UK THEN
      BEGIN
        FOR E IN U
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
  ELSIF UPPER(OPERATION) IN ('ENABLE') THEN
    IF PK THEN
      BEGIN
        FOR E IN P
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF FK THEN
      BEGIN
        FOR E IN P
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
      BEGIN
        FOR E IN R
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF UK THEN
      BEGIN
        FOR E IN U
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE('the first parameter of the procedure must be DROP or ENABLE or DISABLE');
  END IF;
END;