How to disable foreign key constraints in postgresql

Question

I'm using AWS Aurora Postgres and using DMS to migrate from RDS postgres to Aurora PG. In order to perform the FULL LOAD I want to disable foreign key constraints and triggers on all the objects. I'm able to disable triggers but couldn't find a way to disable constraints.

Below doesn't work:

ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey;

It throws:

ERROR: syntax error at or near "CONSTRAINT" LINE 1: ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey; ^ SQL state: 42601 Character: 30

Setting "session_replication_role" = "replica" in the parameter group didn't work. While the DMS task tries to truncate the table part of preparation it still fails with foreign key violation errors.

Please advise any workarounds.

Note: I couldn't do below since in RDS I do not have permissions to do so even with master account:

alter table so_items disable trigger ALL;

ERROR: permission denied: "RI_ConstraintTrigger_c_16520" is a system trigger SQL state: 42501

Answer 1

You shouldn't modify the triggers a Postgres constraint relies on. This is an implementation detail for which you shouldn't care about.

You cannot disable constraints, really.

To turn constraints temporarily off, you can defer the constraint check to the end of transactions:

ALTER TABLE so_items ALTER CONSTRAINT so_items_so_id_fkey DEFERRABLE INITIALLY DEFERRED;

With that modification the constraint is evaluated after a modification at the end of the current transaction. This will allow you to break the constraint inside of a transaction.

You may DROP CONSTRAINTs

ALTER TABLE so_items DROP CONSTRAINT so_items_so_id_fkey;

which will delete it permanently.

Edit: It is also possible to disable the triggers which also affects the foreign key constraints of the table

ALTER TABLE so_items DISABLE TRIGGER ALL;

But when you are re-enabling the triggers afterwards, the foreign keys are not checked. This might lead to invalid / inconsistent foreign keys in the database.