How to disable Postgres foreign key checks Temporarily?

There is no doubt that Postgres is highly acid compliant. With that, it brings a little overhead in performance as well as little unfriendliness.

I was using Postgres in one of my projects and I had to seed the database. While seeding I would truncate the table and fill in the data. But Postgres wouldn’t allow me to truncate data when a foreign key is present.

There is one more way in recent postgres versions where you can truncate the tables with cascading. Which would basically delete all your foreign keys records as well. Even if you have specified ON DELETE NO ACTION in your foreign keys. It can be achieved using the following syntax.

TRUNCATE TABLE "table_name" CASCADE

But what I want is to not delete other tables data, but to disable foreign key checks temporarily. Well, let’s do that.

But in Postgres, there is no direct way of disabling foreign key checks for the database. But we can do it by disabling Triggers on a table.

ALTER TABLE table_name DISABLE TRIGGER ALL;

and after your other database operations remember to put them back using

ALTER TABLE table_name ENABLE TRIGGER ALL;

Programmer, Hacker, Trainor, Speaker, and Blogger. Opensource Lover. Built LeHacker.com.

Leave a reply:

Your email address will not be published.

Site Footer