Disable & Enable Foreign key constraint through procedure

Sometimes DBA or Developer need to import huge number of data in database in bulk which is difficult if foreign key constraint is enable in schema which create conflict between the table so using procedure can be disable foreign key constraint of all schema and after completed the import task can be enable foreign key at the same time.

During the execution of script it will ask the schema name for which user want to disable foreign key constraint.

CREATE OR REPLACE PROCEDURE KEY_DISABLE_ALL (IN IN_SCHEMA_NAME VARCHAR(100), OUT getResult VARCHAR(6000))
LANGUAGE SQL
BEGIN
declare v_tabname varchar(100);
declare v_constname varchar(100);
declare v_rows integer;
declare v_sql varchar(200);
declare v_schema_name varchar(200);

declare temp_cursor cursor for SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = IN_SCHEMA_NAME ORDER BY CONSTNAME,TABNAME, REFTABNAME;
select count(*) into v_rows from syscat.references WHERE TABSCHEMA = IN_SCHEMA_NAME;

open temp_cursor;

while (v_rows > 0) DO
FETCH temp_cursor INTO v_tabname, v_constname;
SET v_sql ='alter table ' || IN_SCHEMA_NAME || '.' || v_tabname ||  ' alter foreign key ' || v_constname || ' NOT ENFORCED';
execute immediate v_sql;

SET v_rows = v_rows - 1;
end while;
close temp_cursor;
END

After finish the task user can enable all schema through below script with provide the Schema name.

CREATE OR REPLACE PROCEDURE KEY_ENABLE_ALL (IN IN_SCHEMA_NAME VARCHAR(100))
LANGUAGE SQL
BEGIN
declare v_tabname varchar(200);
declare v_constname varchar(200);
declare v_rows integer;
declare v_sql varchar(200);

declare temp_cursor cursor for SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = IN_SCHEMA_NAME ORDER BY CONSTNAME,TABNAME, REFTABNAME;
select count(*) into v_rows from syscat.references WHERE TABSCHEMA = IN_SCHEMA_NAME;

open temp_cursor;

while (v_rows > 0) DO
FETCH temp_cursor INTO v_tabname, v_constname;
SET v_sql = 'alter table ' || IN_SCHEMA_NAME || '.' || v_tabname ||  ' alter foreign key ' || v_constname || ' ENFORCED';
execute immediate v_sql;

SET v_rows = v_rows - 1;
end while;
close temp_cursor;
END

Disable & Enable Foreign key constraint through procedure

Tags