How to disable foreign key constraints in postgresql

RMu picture RMu · Jan 19, 2018 · Viewed 13.1k times · Source

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

clemens picture clemens · Jan 19, 2018

You cannot disable constraints. This would make no sense.

You may DROP CONSTRAINTs

ALTER TABLE so_items DROP CONSTRAINT so_items_so_id_fkey;

which will delete it permanently, or 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 an transaction.

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

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.