Question 89939 in PostgreSQL Question posted in
The official documentation can be found here.

How to temporarily disable all constraints of a schema in postgresql?

I need to temporarily disable all constraints and triggers of a schema in order to anonymize data in several tables. As there are dependencies between tables, I prefer to disable everything and once the anonymization treatment is over I can enable all constraints and triggers one more time.

I tried SET FOREIGN_KEY_CHECKS=0; and I got this error:

ERROR: unrecognized configuration parameter "foreign_key_checks"
SQL state: 42704

I’ve been reading a lot about that and some people say this is not possible.

Do you know a way to do that?

Thank you!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found this solution,

    I created a temporal table to keep all constraints definition:

    CREATE TEMPORARY TABLE temp_constraints AS
SELECT conname constraintname, conrelid::regclass tablename, pg_get_constraintdef(oid) definition, contype 
FROM pg_catalog.pg_constraint;

    Then I drop all constraints:

    DO $$
DECLARE constraint_name TEXT;
DECLARE constraint_table TEXT;
BEGIN
    FOR constraint_name, constraint_table IN
    SELECT constraintname ,  tablename FROM temp_constraints ORDER BY contype DESC
        LOOP
            EXECUTE 'ALTER TABLE ' || constraint_table || ' DROP CONSTRAINT IF EXISTS ' || constraint_name || ' CASCADE;';
        END LOOP;
END $$;

    And after anonymizing data I restore all constraints using the definitions in the temporal table and I drop the temporal table:

    DO $$
DECLARE constraint_table TEXT;
DECLARE constraint_definition TEXT;
BEGIN
    FOR constraint_table, constraint_definition IN 
    SELECT tablename, definition FROM temp_constraints ORDER BY contype DESC
        LOOP
            EXECUTE 'ALTER TABLE ' || constraint_table || ' ADD ' || constraint_definition || ';';
        END LOOP;
    DROP TABLE IF EXISTS temp_constraints;
END $$;

    I hope this can help someone else. Thank you!


  2. 0

    To disable foreign keys and deferrable unique and primary key constraints for a table, you can use

    ALTER TABLE ... DISABLE TRIGGER ALL;

    To disable all such constraints for the duration of a database session, you can

    SET session_replication_role = replica;

    Both tricks won’t work for non-deferrable constraints and check constraints.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search