Lists: | pgsql-performance |
---|
From: | "Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Partitioned Tables Foreign Key Constraints Problem |
Date: | 2008-07-24 10:15:41 |
Message-ID: | 7e4ba9550807240315q22c955a7t4c3895b94aa2138d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Hi all,
Have a problem related to partition tables.
We have the following schema's :
master -
id integer (PRIMARY KEY)
cid integer
child1, child2 are the child tables of master table. Here "cid" is the field
used for partitioning.
We have another table called
other_tbl -
id integer (PRIMARY KEY)
m_id integer (FOREIGN KEY FROM master(id))
Now the problem is, since other_tbl->m_id referenced from master->id, if we
try to insert any vales in "other_tbl" for m_id its checks it presence in
only master table and not in its child table. As the master table is always
empty in partitions tables a foreign key violation ERROR is being given.
How can we define the Foreign_key constraint on "other_tbl" based upon any
partitioned table field.
--
Regards
Gauri
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Gauri Kanekar <meetgaurikanekar(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Partitioned Tables Foreign Key Constraints Problem |
Date: | 2008-07-25 22:31:55 |
Message-ID: | 488A545B.4070609@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Gauri,
> How can we define the Foreign_key constraint on "other_tbl" based upon
> any partitioned table field.
Currently PostgreSQL doesn't have any simple way to support this. There
are a few workarounds, but they're fairly awkward, such as a trigger to
check if the ID is correct.
--Josh