Hello,
I have a problem about dropping and recovering foreign key constraint.
Since we are using table partitioning for table A, records are always directed
to one partition by triggers, leaving the parent table A empty.
However, for table B where A's id serves as a foreign key, the foreign key constraint
tells the database to look into the parent table (A), which is empty.
This will cause the violation of FK-constraint when inserting into table B.
The previous way to solve this problem is by issuing commands like: ALTER table exon_exon_junc_obs disable trigger all
;
It doesn't work in another server where I am not the superuser.
I have tried to use ""ALTER table table_B_name DROP CONSTRAINT constraint_name;"""
It worked but I can not recover the foreign key constraint after inserting rows.
The command
"""ALTER table table table_B_name ADD CONSTRAINT constraint_name FOREIGN KEY (A_id) REFERENCES A(A_id)"""
returns
ERROR: insert or update on table "B" violates foreign key constraint "constraint_name"
DETAIL: Key (A_id)=(1) is not present in table "A".
Have you ever encountered a similar problem? Any possible solutions to it?
Thank you in advance!
Xiaoning