Skip navigation.

Disabling constraints before the import operation

Some times you need to disable user constraints before doing the import for some user,
for this reason i created a procedure that can be used to disable the constraints before the import,and than use it again to enable the constraints after doing the import,and also this procedure can be used to drop user constraints when needed.
I named it MANAGE_USER_FK_PK_UK,note the order in the name FK_PK_UK,because when you want to disable constraints you should disable the foreign keys after doing that for the primary keys,for the unique keys it can be done at first or not,i do it at last.
The procedure have 4 parameters:
- OPERATION VARCHAR2
- FK BOOLEAN DEFAULT TRUE
- PK BOOLEAN DEFAULT TRUE
- UK BOOLEAN DEFAULT TRUE
The first parameter OPERATION must be in ('ENABLE','DISABLE','DROP'),this indicates the operation desired to be run.
The second parameter,if true it will be applied to foreign keys else no
The third parameter,if true it will be applied to primary keys else no
The forth parameter,if true it will be applied to unique keys else no

-- MANAGE_USER_FK_PK_UK.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';

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;
/

so for import,we want to disable the constraints of the user we disired to import,
create the procedure under this user,suppose it's SCOTT
usage:
MANAGE_USER_FK_PK_UK.SQL('DISABLE','TRUE','TRUE','TRUE');
Doing the import operation
MANAGE_USER_FK_PK_UK.SQL('ENABLE','TRUE','TRUE','TRUE');

This will prevent getting errors related to constraints during import

Happy coding