| 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: | Whole Thread | Raw Message | 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 |