This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_Students ( StudID INTEGER PRIMARY KEY ,StudName CHARACTER VARYING ,StudClass CHAR(1) ); |
Insert few sample records in Student Master table:
|
1 2 3 4 |
INSERT INTO tbl_Students VALUES (1,'Anvesh','A') ,(2,'Neevan','B'),(3,'Jenny','C') ,(4,'Roy','C'),(5,'Martin','C'); |
Create a sample Student Marks table:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_StudentMarks ( StudentMarkID INTEGER PRIMARY KEY ,StudID INTEGER ,Sub1Mark SMALLINT ,Sub2Mark SMALLINT ,Sub3Mark SMALLINT ,CONSTRAINT fk_tbl_StudentMarks_StudID FOREIGN KEY (StudID) REFERENCES tbl_Students (StudID) ); |
Try to insert data with StudID which does not belong to Student Master table:
|
1 2 |
INSERT INTO tbl_StudentMarks VALUES (1,6,50,60,90); |
You will get a below error message:
|
1 2 3 |
ERROR: insert or update on table "tbl_studentmarks" violates foreign key constraint "fk_tbl_studentmarks_studid" DETAIL: Key (studid)=(6) is not present in table "tbl_students". ********** Error ********** |
Disable all hidden triggers of a Student Mark table:
|
1 |
ALTER TABLE tbl_StudentMarks DISABLE TRIGGER ALL; |
Now Again, try to insert same record:
|
1 2 |
INSERT INTO tbl_StudentMarks VALUES (1,6,50,60,90); |
Once you did with your task, please enable all trigger of a table:
|
1 |
ALTER TABLE tbl_StudentMarks ENABLE TRIGGER ALL; |
