Category Archives: foreign key

Recreate Foreign Keys in PostgreSQL

Function I devised to allow recreating all foreign keys and switching if they cascade:

CREATE OR REPLACE FUNCTION util_recreate_fks (allow_cascade bool) RETURNS bool AS $$
DECLARE
constraint_cursor CURSOR FOR
SELECT t.constraint_name, t.table_schema || '.' || t.table_name as table_name, --t.constraint_type,
c1.table_schema || '.' || c1.table_name as table_name_pk, c1.column_name as column_name_pk,
c2.table_name as table_name_fk, c2.column_name as column_name_fk
FROM information_schema.table_constraints t,
information_schema.constraint_column_usage c1,
information_schema.key_column_usage c2
WHERE t.constraint_name = c1.constraint_name
AND t.constraint_name = c2.constraint_name
AND t.constraint_type = 'FOREIGN KEY';
_constraint RECORD;
cascade_setting VARCHAR;
BEGIN
IF (allow_cascade) THEN
cascade_setting := 'CASCADE';
ELSE
cascade_setting := 'NO ACTION';
END IF;

OPEN constraint_cursor;

LOOP
FETCH constraint_cursor INTO _constraint;
EXIT WHEN NOT FOUND;
EXECUTE 'ALTER TABLE ' || _constraint.table_name || ' DROP CONSTRAINT ' || _constraint.constraint_name;
EXECUTE '
ALTER TABLE ' || _constraint.table_name || '
ADD CONSTRAINT ' || _constraint.constraint_name || ' FOREIGN KEY (' || _constraint.column_name_fk || ')
REFERENCES ' || _constraint.table_name_pk || '(' || _constraint.column_name_pk || ')
ON UPDATE ' || cascade_setting || '
ON DELETE ' || cascade_setting || '
';
END LOOP;

CLOSE constraint_cursor;

RETURN allow_cascade;
END;
$$ LANGUAGE 'plpgsql';