Обсуждение: Using duplicate foreign keys
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.
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)
> 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? public.commontable describes rules how invoices are posted to general ledger. It seems not reasonable to duplicate this table in every company schema since it contains usually same data for every company. public.commontable contians fixed account numbers which I think I must force to be present in chart of accounts in every company schema to use this kind of foreign key relationship. > 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. public commontable contains also a lot of other columns describing invoice posting rules. accountnumber cannot be primary key of public.commontable since it can contain same account numbers for different types of documents. Yes, it seems to be possible to create a new table public.commonaccountnumbers with accountnumber as primary key. So the question seems to be: can I use duplicate foreign keys without problems or must I waste resources to create new table commonaccountnumbers ? Andrus.