populating database, partition table, foreign key constraint Error

From: Xiaoning Xu <xixu(at)bcgsc(dot)ca>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: populating database, partition table, foreign key constraint Error
Date: 2012-01-27 18:03:36
Message-ID: 20FBD68BF7F3264B889B082B48A82FD66507847F07@MORBO.phage.bcgsc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Giuseppe Sacco 2012-01-27 18:28:44 Re: error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE
Previous Message Tom Lane 2012-01-27 17:38:10 Re: error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE