Generating a script that disables foreign keys in many tables

Question

I'm working on a refresh script where we

1. DROP the table constraints,
2. TRUNCATE the table,
3. INSERT data in the table and
4. ADD the constraints that are dropped at the first step.

I created the following SQL using dynamic SQL to genarate a SQL DROP foreign key constraint so that I can execute for multiple tables I'm working on,

SELECT 
'ALTER TABLE  SSP2_PCAT.' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE 
UPPER(CONSTRAINT_SCHEMA) = 'SSP2_PCAT' AND
UPPER(TABLE_NAME) IN (RATES,.....);

The output of above SQL is going to be as follows,

ALTER TABLE SSP2_PCAT.RATES DROP CONSTRAINT fk_rate;

Mentioned above truncate is done as its very straight forward.

Used the WITH clause with STRING_AGG function by enabling the DBlink Extensions in the POSTGRES similarly using Dynamic SQL so that it gives SQL for multiple tables at one shot.

Now I'm working on a similar query to ADD the constraints as follows, (that are dropped in the first step),

SELECT DISTINCT 'ALTER TABLE  ' || cs.TABLE_NAME || 
' ADD CONSTRAINT ' ||rc.CONSTRAINT_NAME ||
' FOREIGN KEY ' ||c.COLUMN_NAME ||
' REFERENCES ' ||cs.TABLE_NAME || ' (' || cs.CONSTRAINT_NAME || ') ' || ' ON UPDATE ' || rc.UPDATE_RULE || ' ON DELETE ' || rc.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS, INFORMATION_SCHEMA.COLUMNS C
WHERE cs.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND
cs.TABLE_NAME = c.TABLE_NAME AND
UPPER(cs.TABLE_NAME) = 'ADDITIONAL_RULES' AND
UPPER(cs.TABLE_SCHEMA) = 'SSP2_PCAT';

But unfortunately this query is not giving the desired result, looks like I'm missing something as its especially when selecting the c.COLUMN_NAMEfield instead of the references field it's giving all the available fields in the table, also it's not giving the name of the Parent_table cs.TABLE_NAME rather it's giving the same table_name. Can someone please keep me posted if I'm missing any join from any other data dictionary tables?

Answer 1

You don't need to drop and recreate foreign keys. Use deferred constraints instead. It's enough when you define foreign keys as deferrable initially deferred and execute all inserts in a single transaction. There are more options, however, so read about deferrable constraints in the documentation:

• CREATE TABLE
• SET CONSTRAINTS

---

Use the system catalog pg_constraint.

You can generate a script to alter foreign keys to DEFERRABLE INITIALLY DEFERRED in this way:

select format(
    'ALTER TABLE %s ALTER CONSTRAINT %s DEFERRABLE INITIALLY DEFERRED;', 
    conrelid::regclass::text, 
    conname)
from pg_constraint
where contype = 'f' 
and conrelid = any(array['table1', 'table2', 'table3']::regclass[])
and connamespace = 'ssp2_pcat'::regnamespace;

You can run the script once. Then your import script may look like this:

BEGIN;
TRUNCATE table1;
TRUNCATE table2;
...
INSERT INTO table1...
INSERT INTO table2...
...
COMMIT;

---

If for some reason you cannot use deferred constraints, use the script to drop constraints:

select
    format(
        'ALTER TABLE %s DROP CONSTRAINT %s;', 
        conrelid::regclass::text, 
        conname)
from pg_constraint
where contype = 'f' 
and conrelid = any(array['table1', 'table2', 'table3']::regclass[])
and connamespace = 'ssp2_pcat'::regnamespace;

and to recreate them:

select
    format(
        'ALTER TABLE %s ADD CONSTRAINT %s %s;', 
        conrelid::regclass::text, 
        conname, 
        pg_get_constraintdef(oid))
from pg_constraint
where contype = 'f' 
and conrelid = any(array['table1', 'table2', 'table3']::regclass[])
and connamespace = 'ssp2_pcat'::regnamespace;