Re: Using duplicate foreign keys

Andrus wrote:
> I have multi-company database.
> Each company has its own chart of accounts table which are stored in each
> company schema.
> Some account numbers are used in a common table which is stored in public
> schema.
>
> So I need to create duplicate foreign keys like
>
> create temp table company1.chartoffaccounts ( accountnumber int primary
> key);
> create temp table company2.chartoffaccounts ( accountnumber int primary
> key);
>
> create temp table public.commontable ( accountnumber int,
>    FOREIGN KEY (accountnumber)   REFERENCES c1hartoffaccounts
> (accountnumber)
>    FOREIGN KEY (accountnumber)   REFERENCES c2hartoffaccounts
> (accountnumber) );
>
>
> Is it OK to use duplicate foreign keys ? What issues will they cause ?
>
> Andrus.
>
The problem I see with that is that any value of accountnumber in
public.commontable would need to be in both company1.chartoffaccounts
and company2.chartoffaccounts.  One key referencing two completely sets
of data?  That sounds broken.  Can you give a more detailed example of
how you want to use this?   Off hand, it sounds like what you may want
is to have accountnumber as the primary key of public.commontable with
the accountnumber columns in the schema specific tables referencing it
instead.

--
erik jones <erik@myemma.com>
software development
emma(r)