PostgreSQL: How to Disable or Enable Foreign Key Constraint of a Table

In this post, I am sharing one option to Disable / Enable the Foreign Key Constraint in PostgreSQL.

During data migration and testing purpose, Database Developer requires to disable Foreign key constraint of a Table.

Once you disable constraint, then later you might need to enable again, but during this exercise make sure that all your data changes are correct.

PostgreSQL does not provide any direct command or function to disable / enable the Foreign key constraints.

When you create any Foreign Key on the table, internally It creates a hidden trigger for check data integrity.
You should enable/disable the trigger for achieving disable foreign key constraint.

Create a sample Student Master table:

Insert few sample records in Student Master table:

Create a sample Student Marks table:

Try to insert data with StudID which does not belong to Student Master table:

You will get a below error message:

Disable all hidden triggers of a Student Mark table:

Now Again, try to insert same record:

Once you did with your task, please enable all trigger of a table:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of