Hi Im engineering a PG database with my workmate.
Unfortunately the inheritance feature seems not to be able to solve my problem.
I have a table "order" with an orderID, each order has 1 to n types of articles, like services and supply articles with the same PK.
The PK is order_number and position, the PK must be unique over all article-tables. All articles have 1 to n packages.
I have a table art as the "mother-table". All article-tables inherit the PK and some other columns from that table.
My problem is now that i dont know how i can make the PK on art unique and make a FK from packages to art.

At the moment im thinking about a workaround using triggers, but i wanted to know some other opinions on that matter first.

Thanks in advance,

Mario



---------------------------------
NEU: Fragen stellen - Wissen, Meinungen und Erfahrungen teilen. Jetzt auf Yahoo! Clever.

On Tue, Dec 19, 2006 at 11:20:35 +0100,
Udo Zubel <mariorr88@yahoo.de> wrote:
> Hi Im engineering a PG database with my workmate.
> Unfortunately the inheritance feature seems not to be able to solve my problem.
> I have a table "order" with an orderID, each order has 1 to n types of articles, like services and supply articles with the same PK.
> The PK is order_number and position, the PK must be unique over all article-tables. All articles have 1 to n packages.
> I have a table art as the "mother-table". All article-tables inherit the PK and some other columns from that table.
> My problem is now that i dont know how i can make the PK on art unique and make a FK from packages to art.
>
> At the moment im thinking about a workaround using triggers, but i wanted to know some other opinions on that matter first.

Is there a table of valid articles?

If so, then the mother table should have foreign key references to order and
the article tables and both of these references should be declared as the
primary key.

If not, and you can use any old numbers for the article, then you might want
use a sequence for the article number. If you do this, these will be unique
in themselves, so that in combination with the order_number they will still
be unique. And doing things this way will be a lot simpler than trying to
maintain a separate pool for each order_number.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly