Below function fn_get_fk() used to return the FOREIGN KEY constraints with create/drop statement.
This function will minimize PostgreSQL DBA's work to find all the referencing table to drop the constraint one by one manually.
Table name and Column names are the parameter for this function, it generate and return query to, CREATE/DROP CONSTRAINT statement for the referencing table (child table) for the given referenced table(parent table).
CREATE or REPLACE FUNCTION fn_get_fk(tbl_name varchar, col_name varchar) RETURNS varchar
LANGUAGE plpgsql
AS $
-- DECLARE VARIABLE
DECLARE
Vtbl varchar(150);
Vqrys varchar(150);
Vcol varchar(150);
Vcon varchar(150);
Vqryd varchar(300);
Vqrya varchar(300);
Vsch varchar(150);
VqryAdd varchar(30000) = '';
VqryDrp varchar(30000) = '';
Vrtn varchar;
-- CREATE CURSOR
Ctbl_list refcursor;
Ctblcon information_schema.table_constraints %rowtype;
Vtblkcu information_schema.key_column_usage;
BEGIN
-- OPEN CURSOR WITH REFERENCING TABLE FOR GIVEN PARENT TABLE
open Ctbl_list for execute 'SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('||quote_literal(tbl_name)||')
and ccu.column_name in ('||quote_literal(col_name)||')
WHERE lower(tc.constraint_type) in (''foreign key'');' ;
loop fetch next from Ctbl_list into Ctblcon ;
exit when not found;
Vtbl = Ctblcon.table_name;
Vcon = Ctblcon.constraint_name;
Vsch = Ctblcon.constraint_schema;
--GENERATE DROP CONSTRAINT STATEMENT
Vqryd = 'alter table '|| Vsch||'.'||Vtbl ||' drop constraint ' || Vcon;
VqryDrp = VqryDrp ||';'||chr(10)||Vqryd;
raise info 'Qry : %; ',Vqryd;
--GET REFRENCING COLUMN NAME
Vqrys = 'select * from information_schema.key_column_usage where constraint_name='||quote_literal(Vcon) ;
EXECUTE Vqrys into Vtblkcu;
-- GENERATE ADD CONSTRAINT STATEMENT
Vqrya = 'alter table '|| Vsch||'.'||Vtbl ||' add constraint ' || Vcon ||' foreign key('||Vtblkcu.column_name||')
references personal.m_pis_master(str_empno)';
raise notice 'add constraint : %; ',Vqrya;
VqryAdd = VqryAdd || chr(10) || Vqrya || ';';
end loop;
-- CONCATENATE BOTH ADD & DROP STATEMENT
Vrtn ='DROP CONSTRAINT : '|| VqryDrp ||chr(10)||'ADD CONSTRAINT : '||chr(10)||VqryAdd;
RETURN Vrtn;
END;
$;
in pgAdmin, to Execute the function with write output to file option,
SELECT * FROM fn_get_fk('employee_master','str_empno')
in command mode
copy (select * from fn_get_fk('employee_master','str_empno')) to '/root/Desktop/Constraint.txt'
this will return all the foreign key constraint which is referencing the column str_empno on employee_master table.
output looks like
DROP CONSTRAINT :
alter table personal.m_authority drop constraint m_authority_str_empno_fkey;
alter table personal.m_pis drop constraint m_pis_str_empno_fkey;
alter table tours.d_tour_info drop constraint d_tour_info_str_empno_fkey
ADD CONSTRAINT :
alter table personal.m_authority add constraint m_authority_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno);
alter table personal.m_pis add constraint m_pis_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno);
alter table tours.d_tour_info add constraint d_tour_info_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno),
