Thursday, July 24, 2008

Enable & disable foreign keys in Oracle


CREATE OR REPLACE PROCEDURE DIS_FK_CONSTR as
CURSOR c iS
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R' AND STATUS = 'ENABLED';

BEGIN
FOR i IN c LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.TABLE_NAME ||
' DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
END LOOP;

END DIS_FK_CONSTR;
/


CREATE OR REPLACE PROCEDURE ENA_FK_CONSTR as
CURSOR c iS
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R' AND STATUS = 'DISABLED';

BEGIN
FOR i IN c LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.TABLE_NAME ||
' ENABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
END LOOP;

END ENA_FK_CONSTR;
/