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
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 |