Disable PostgreSQL foreign key checks for migrations

I'm creating a lot of migrations that have foreign keys in PostgreSQL 9.4.

This is creating a headache because the tables must all be in the exact order expected by the foreign keys when they are migrated. It gets even stickier if I have to run migrations from other packages that my new migrations depend on for a foreign key.

In MySQL, I can simplify this by simply adding SET FOREIGN_KEY_CHECKS = 0; to the top of my migration file. How can I do this temporarily in PostgresSQL only for the length of the migration code?

BTW, using the Laravel Schema Builder for this.

Solution 1:

For migration, it is easier to disable all triggers with:

SET session_replication_role = 'replica';

And after migration reenable all with

SET session_replication_role = 'origin';

Solution 2:

PostgreSQL doesn't support any configuration option, but there is another possibility.

postgres=# \d b
        Table "public.b"
┌────────┬─────────┬───────────┐
│ Column │  Type   │ Modifiers │
╞════════╪═════════╪═══════════╡
│ id     │ integer │           │
└────────┴─────────┴───────────┘
Foreign-key constraints:
    "b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) DEFERRABLE

The referential integrity in Postgres is implemented by triggers, and you can disable triggers on table. With this method you can upload any data (risk), but it is significantly faster - because the check over large data is expensive. And if your upload is safe, then you can do it.

BEGIN;
ALTER TABLE b DISABLE TRIGGER ALL;
-- now the RI over table b is disabled
ALTER TABLE b ENABLE TRIGGER ALL;
COMMIT;

Next possibility is using deferred constraints. This move constraint check to commit time. So you should not to respect order with INSERT commands:

ALTER TABLE b ALTER CONSTRAINT b_id_fkey DEFERRABLE;

BEGIN
postgres=# SET CONSTRAINTS b_id_fkey DEFERRED;
SET CONSTRAINTS
postgres=# INSERT INTO b VALUES(100); -- this is not in a table
INSERT 0 1
postgres=# INSERT INTO b VALUES(10);
INSERT 0 1 
postgres=# COMMIT;
ERROR:  insert or update on table "b" violates foreign key constraint "b_id_fkey"
DETAIL:  Key (id)=(100) is not present in table "a".

This method should be preferred for you, because the inserted data will be checked.