On Tue, Dec 19, 2006 at 11:20:35 +0100, Udo Zubel <mariorr88@xxxxxxxx> 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.
