1

So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.

The drop constraints function works by selecting various "deferrable constraint levels" into respective temp tables (e.g. temp tables for: fully_deferrable, not_deferrable, semi_deferrable) to make sure that information exists for the subsequent recreate constraints function. I am running into an error though that will not allow me to create the constraint because this constraint already exists. So the recreate constraints function will error out with something like: " ERROR: constraint "avg_test_k_school_fkey" for relation "avg_test" already exists", even though I have already dropped this foreign constraint via the previous function.

I have checked the tables to make sure all the foreign constraints are dropped, and when I run the ALTER TABLE statement outside of the function, the foreign constraint is added with no problem. So I am wondering what in the world is going on with executing this from the function. I am thinking that perhaps I am doing something wrong control-flow wise, or that these operations are not possible to call from functions. Please let me know if you have any questions!

This function is for the recreation of foreign keys. not_deferrable_constraints is a temp table that has the child table, parent table, whether or not a constraint is deferred, the fields of the primary/foreign keys, and the name of the constraint.

Here is the SQL:

 CREATE OR REPLACE FUNCTION test.recreate_all_foreign_constraints(schema_name name)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare 
_tbl record;
add_constraint record;
begin

--Recreate not_deferrable_constraints
for _tbl in(select child_table from not_deferrable_constraints) loop
    for add_constraint in(select constraint_name, fk_column, pk_column, parent_table from not_deferrable_constraints where child_table = _tbl.child_table) loop
        execute 'ALTER TABLE ' ||schema_name||'.'|| _tbl.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')';
    end loop;
end loop;
end; 
$function$
;

It would be super cool if this worked! I'm so close I can feel it!! Thanks for your help!

  • Do you happen to call both functions -- the one that drops constraints and the one that adds them back -- in the same transaction? – mustaccio Aug 14 at 20:19
  • I do not currently. As I'm developing, I am running them in separate transactions. Which makes this more curious. In the future I foresee reworking this workflow a bit. – Eshara Mondal Aug 14 at 20:22
  • This did it!! What the heck. haha. Thank you! Is there a way to mark your answer as the answer? – Eshara Mondal Aug 14 at 20:28
  • Yes, there is ;) – ypercubeᵀᴹ Aug 14 at 20:30
3

I think you need:

for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop 

If a table has more than one constraints, your code will try to recreate each one of them many times.

Or just use a single for loop, I don't think you need the nesting.

--Recreate not_deferrable_constraints
for add_constraint in (select constraint_name, fk_column, pk_column, parent_table, child_table 
                       from not_deferrable_constraints) loop
    execute 'ALTER TABLE ' ||schema_name||'.'|| add_constraint.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')';
end loop;
  • 2
    Also note that FK constraints can be devised from many columns. Your code wouldn't work with those. – ypercubeᵀᴹ Aug 14 at 20:36
  • Are you talking about multi-column FK constraints? Or multiple FK constraints on one table? – Eshara Mondal Aug 14 at 20:51
  • 2
    Multi-column FKs – ypercubeᵀᴹ Aug 14 at 20:52
  • Ah yes, definitely wouldn't. Thanks for looking out! – Eshara Mondal Aug 14 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.